Rocksolid Light

Welcome to novaBBS (click a section below)

mail  files  register  newsreader  groups  login

Message-ID:  

The only way to learn a new programming language is by writing programs in it. -- Brian Kernighan


computers / comp.sys.tandem / Re: SQL Base Table - EOF is almost Full

SubjectAuthor
* SQL Base Table - EOF is almost FullBry Pas
`* Re: SQL Base Table - EOF is almost FullBill Honaker
 +- Re: SQL Base Table - EOF is almost FullKeith Dick
 `* Re: SQL Base Table - EOF is almost FullBry Pas
  `* Re: SQL Base Table - EOF is almost FullJShepherd
   `* Re: SQL Base Table - EOF is almost FullRandall
    `* Re: SQL Base Table - EOF is almost FullINDRANIL CHAKRABORTY
     `* Re: SQL Base Table - EOF is almost FullBry Pas
      `- Re: SQL Base Table - EOF is almost FullKeith Dick

1
SQL Base Table - EOF is almost Full

<46c8534a-b3da-4fb2-b0f1-0f9c3a0107d3n@googlegroups.com>

 copy mid

https://www.novabbs.com/computers/article-flat.php?id=360&group=comp.sys.tandem#360

 copy link   Newsgroups: comp.sys.tandem
X-Received: by 2002:a7b:c8c2:0:b0:37b:b742:a9de with SMTP id f2-20020a7bc8c2000000b0037bb742a9demr1766443wml.37.1645507265522;
Mon, 21 Feb 2022 21:21:05 -0800 (PST)
X-Received: by 2002:a0d:da45:0:b0:2d0:bd53:b39 with SMTP id
c66-20020a0dda45000000b002d0bd530b39mr21827786ywe.463.1645507265041; Mon, 21
Feb 2022 21:21:05 -0800 (PST)
Path: i2pn2.org!i2pn.org!weretis.net!feeder8.news.weretis.net!proxad.net!feeder1-2.proxad.net!209.85.128.88.MISMATCH!news-out.google.com!nntp.google.com!postnews.google.com!google-groups.googlegroups.com!not-for-mail
Newsgroups: comp.sys.tandem
Date: Mon, 21 Feb 2022 21:21:04 -0800 (PST)
Injection-Info: google-groups.googlegroups.com; posting-host=175.176.30.30; posting-account=zrnrigoAAABeE5VxiOwZ7Zq0V2u9uEUs
NNTP-Posting-Host: 175.176.30.30
User-Agent: G2/1.0
MIME-Version: 1.0
Message-ID: <46c8534a-b3da-4fb2-b0f1-0f9c3a0107d3n@googlegroups.com>
Subject: SQL Base Table - EOF is almost Full
From: bryanpas...@gmail.com (Bry Pas)
Injection-Date: Tue, 22 Feb 2022 05:21:05 +0000
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
 by: Bry Pas - Tue, 22 Feb 2022 05:21 UTC

Hello Tandem Experts and Enthusiast,

I would like to to ask for your help and ask for guidance on how to reduce the EOF usage for the file we have (it says SQL Base Table). We have a table that monitors its usage and can see that Partition 1 is at 80.4% already, while Partition 2 is 0.2%. The solutions that I have in mind is to delete some of the old datas in the SQL file, not sure though if it will reduce the usage. Comments and suggestions are very appreciated, cheers everyone.

ESCRIPTON VOLUME LOCATION EOF STATUS
Partition 0 $DS02.prdaSQL.CMCMBRT 30.7%
Partition 1 $DS03.prdaSQL.CMCMBRT 80.4%
Partition 2 $DS04.prdaSQL.CMCMBRT 0.2%
Partition 3 $DS05.prdaSQL.CMCMBRT 33.8%

Index 1 $DS02.prdaSQL 24.2%
Index 2 $DS02.prdaSQL 50.2%
Index 3 $DS02.prdaSQL 36.5%

Below is the fileinfo of the SQL table file:

$DS03.PRDASQL.CMCMBRT
SQL BASE TABLE
CATALOG $DS02.PRDASDX
VERSION 2
TYPE K
FORMAT 1
EXT ( 37752 PAGES, 37752 PAGES, MAXEXTENTS 16 )
REC 251
PACKED REC 250
BLOCK 4096
KEY ( COLUMN 0, OFFSET 0, LENGTH 10, ASC,
COLUMN 1, OFFSET 10, LENGTH 19, ASC,
COLUMN 2, OFFSET 29, LENGTH 5, ASC,
COLUMN 3, OFFSET 34, LENGTH 2, ASC )
INDEX ( 1, $DS02.PRDASQL.CMCMBRX1,
COLUMN 11, OFFSET 64, LENGTH 8, ASC,
UNIQUE )
INDEX ( 2, $DS02.PRDASQL.CMCMBRX2,
COLUMN 12, OFFSET 72, LENGTH 17, ASC,
COLUMN 13, OFFSET 89, LENGTH 12, ASC,
NOT UNIQUE )
INDEX ( 3, $DS02.PRDASQL.CMCMBRX3,
COLUMN 30, OFFSET 227, LENGTH 24, ASC,
NOT UNIQUE )
PART ( 0, $DS02, 37752 PAGES, 37752 PAGES, MAXEXTENTS 16, FORMAT 1,
( [ 0, 0, 0, 0, 0, 0, 0, 0, 0, 0 ], [ 0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
0, 0, 0, 0, 0, 0, 0, 0, 0 ], [ 0, 0, 0, 0, 0 ], [ 0, 0 ] ) )
PART ( 1, $DS03, 37752 PAGES, 37752 PAGES, MAXEXTENTS 16, FORMAT 1,
( "08", "510266020", [ 0, 0, 0, 0, 0 ], [ 0, 0 ] ) )
PART ( 2, $DS04, 37752 PAGES, 37752 PAGES, MAXEXTENTS 16, FORMAT 1,
( "08", "592108275", [ 0, 0, 0, 0, 0 ], [ 0, 0 ] ) )
PART ( 3, $DS05, 37752 PAGES, 37752 PAGES, MAXEXTENTS 16, FORMAT 1,
( "08", "592108575", [ 0, 0, 0, 0, 0 ], [ 0, 0 ] ) )
AUDIT
BUFFERED
AUDITCOMPRESS
OWNER 190,1
SECURITY (RWEP): OOOO
SECONDARY PARTITION
DATA MODIF: 18 Feb 2022, 16:12, OPEN
CREATION DATE: 8 Jul 2019, 20:30
REDEFINITION DATE: 5 Jun 2014, 19:43
LAST OPEN: 17 Feb 2022, 22:42
EOF: 993939456 (80.3% USED)
EXTENTS ALLOCATED: 13
INDEX LEVELS: 3

Re: SQL Base Table - EOF is almost Full

<3s9a1hpmtfmj5nkjmq780u3oia9e7cdrsc@4ax.com>

 copy mid

https://www.novabbs.com/computers/article-flat.php?id=361&group=comp.sys.tandem#361

 copy link   Newsgroups: comp.sys.tandem
Path: i2pn2.org!i2pn.org!aioe.org!news.uzoreto.com!newsfeed.xs4all.nl!newsfeed9.news.xs4all.nl!peer01.ams1!peer.ams1.xlned.com!news.xlned.com!peer02.iad!feed-me.highwinds-media.com!news.highwinds-media.com!fx99.iad.POSTED!not-for-mail
From: no_spam_...@x_i_d.com (Bill Honaker)
Newsgroups: comp.sys.tandem
Subject: Re: SQL Base Table - EOF is almost Full
Organization: XID, Ltd.
Message-ID: <3s9a1hpmtfmj5nkjmq780u3oia9e7cdrsc@4ax.com>
References: <46c8534a-b3da-4fb2-b0f1-0f9c3a0107d3n@googlegroups.com>
X-Newsreader: Forte Agent 6.00/32.1186
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Lines: 85
X-Complaints-To: abuse@easynews.com
X-Complaints-Info: Please be sure to forward a copy of ALL headers otherwise we will be unable to process your complaint properly.
Date: Tue, 22 Feb 2022 12:31:58 -0600
X-Received-Bytes: 4701
 by: Bill Honaker - Tue, 22 Feb 2022 18:31 UTC

On Mon, 21 Feb 2022 21:21:04 -0800 (PST), Bry Pas <bryanpascua88@gmail.com> wrote:

>Hello Tandem Experts and Enthusiast,
>
> I would like to to ask for your help and ask for guidance on how to reduce the EOF usage for the file we have (it says SQL Base Table). We have a table that monitors its usage and can see that Partition 1 is at 80.4% already, while Partition 2 is 0.2%. The solutions that I have in mind is to delete some of the old datas in the SQL file, not sure though if it will reduce the usage. Comments and suggestions are very appreciated, cheers everyone.
>
>ESCRIPTON VOLUME LOCATION EOF STATUS
>Partition 0 $DS02.prdaSQL.CMCMBRT 30.7%
>Partition 1 $DS03.prdaSQL.CMCMBRT 80.4%
>Partition 2 $DS04.prdaSQL.CMCMBRT 0.2%
>Partition 3 $DS05.prdaSQL.CMCMBRT 33.8%
>
>Index 1 $DS02.prdaSQL 24.2%
>Index 2 $DS02.prdaSQL 50.2%
>Index 3 $DS02.prdaSQL 36.5%
>
>Below is the fileinfo of the SQL table file:
>
>$DS03.PRDASQL.CMCMBRT
> SQL BASE TABLE
> CATALOG $DS02.PRDASDX
> VERSION 2
> TYPE K
> FORMAT 1
> EXT ( 37752 PAGES, 37752 PAGES, MAXEXTENTS 16 )
> REC 251
> PACKED REC 250
> BLOCK 4096
> KEY ( COLUMN 0, OFFSET 0, LENGTH 10, ASC,
> COLUMN 1, OFFSET 10, LENGTH 19, ASC,
> COLUMN 2, OFFSET 29, LENGTH 5, ASC,
> COLUMN 3, OFFSET 34, LENGTH 2, ASC )
> INDEX ( 1, $DS02.PRDASQL.CMCMBRX1,
> COLUMN 11, OFFSET 64, LENGTH 8, ASC,
> UNIQUE )
> INDEX ( 2, $DS02.PRDASQL.CMCMBRX2,
> COLUMN 12, OFFSET 72, LENGTH 17, ASC,
> COLUMN 13, OFFSET 89, LENGTH 12, ASC,
> NOT UNIQUE )
> INDEX ( 3, $DS02.PRDASQL.CMCMBRX3,
> COLUMN 30, OFFSET 227, LENGTH 24, ASC,
> NOT UNIQUE )
> PART ( 0, $DS02, 37752 PAGES, 37752 PAGES, MAXEXTENTS 16, FORMAT 1,
> ( [ 0, 0, 0, 0, 0, 0, 0, 0, 0, 0 ], [ 0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
> 0, 0, 0, 0, 0, 0, 0, 0, 0 ], [ 0, 0, 0, 0, 0 ], [ 0, 0 ] ) )
> PART ( 1, $DS03, 37752 PAGES, 37752 PAGES, MAXEXTENTS 16, FORMAT 1,
> ( "08", "510266020", [ 0, 0, 0, 0, 0 ], [ 0, 0 ] ) )
> PART ( 2, $DS04, 37752 PAGES, 37752 PAGES, MAXEXTENTS 16, FORMAT 1,
> ( "08", "592108275", [ 0, 0, 0, 0, 0 ], [ 0, 0 ] ) )
> PART ( 3, $DS05, 37752 PAGES, 37752 PAGES, MAXEXTENTS 16, FORMAT 1,
> ( "08", "592108575", [ 0, 0, 0, 0, 0 ], [ 0, 0 ] ) )
> AUDIT
> BUFFERED
> AUDITCOMPRESS
> OWNER 190,1
> SECURITY (RWEP): OOOO
> SECONDARY PARTITION
> DATA MODIF: 18 Feb 2022, 16:12, OPEN
> CREATION DATE: 8 Jul 2019, 20:30
> REDEFINITION DATE: 5 Jun 2014, 19:43
> LAST OPEN: 17 Feb 2022, 22:42
> EOF: 993939456 (80.3% USED)
> EXTENTS ALLOCATED: 13
> INDEX LEVELS: 3

Bryan,

Audited SQL/MP tables such as this can have their partitions 'altered' online, which moves rows from one partition to another.
It reads like whoever defined these partitions either used an old distribution of the data, or took a guess that wasn't quite right.

Look in the SQL/MP Reference manual, in the ALTER TABLE section There are many options here, including ADD , MOVE , REUSE and DROP of a PARTITION.
You probably need to use MOVE.

These operations all work using a TMF transaction... with corresponding LOCKS on the table data.
This should be done by someone with good SQL/MP database management experience.

The first step is to determine what the ideal balance of primary key valuse in the table is, and whether the number of partitions is appropriate.

There is a fairly comprehensive description of how to allow this repartitioning to occur while processing continues, in the 'Considerations' portion of that part of the manual.
You should read that thoroughly and understand the impacts. There are some simple examples at the end of the ALTER TABLE section.

Bill

Re: SQL Base Table - EOF is almost Full

<e0eff885-01be-42f1-a941-6be530b24963n@googlegroups.com>

 copy mid

https://www.novabbs.com/computers/article-flat.php?id=362&group=comp.sys.tandem#362

 copy link   Newsgroups: comp.sys.tandem
X-Received: by 2002:a5d:46d2:0:b0:1e4:b261:7e49 with SMTP id g18-20020a5d46d2000000b001e4b2617e49mr20953916wrs.669.1645562256407;
Tue, 22 Feb 2022 12:37:36 -0800 (PST)
X-Received: by 2002:a0d:e347:0:b0:2d0:f558:c3f4 with SMTP id
m68-20020a0de347000000b002d0f558c3f4mr25492667ywe.109.1645562255850; Tue, 22
Feb 2022 12:37:35 -0800 (PST)
Path: i2pn2.org!i2pn.org!weretis.net!feeder6.news.weretis.net!news.misty.com!border2.nntp.dca1.giganews.com!nntp.giganews.com!news-out.google.com!nntp.google.com!postnews.google.com!google-groups.googlegroups.com!not-for-mail
Newsgroups: comp.sys.tandem
Date: Tue, 22 Feb 2022 12:37:35 -0800 (PST)
In-Reply-To: <3s9a1hpmtfmj5nkjmq780u3oia9e7cdrsc@4ax.com>
Injection-Info: google-groups.googlegroups.com; posting-host=98.42.228.104; posting-account=Lua_ZwoAAADivbVXQF8uKE7dZSUNm9vj
NNTP-Posting-Host: 98.42.228.104
References: <46c8534a-b3da-4fb2-b0f1-0f9c3a0107d3n@googlegroups.com> <3s9a1hpmtfmj5nkjmq780u3oia9e7cdrsc@4ax.com>
User-Agent: G2/1.0
MIME-Version: 1.0
Message-ID: <e0eff885-01be-42f1-a941-6be530b24963n@googlegroups.com>
Subject: Re: SQL Base Table - EOF is almost Full
From: rkd...@gmail.com (Keith Dick)
Injection-Date: Tue, 22 Feb 2022 20:37:36 +0000
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
Lines: 159
 by: Keith Dick - Tue, 22 Feb 2022 20:37 UTC

On Tuesday, February 22, 2022 at 10:32:03 AM UTC-8, Bill Honaker wrote:
> On Mon, 21 Feb 2022 21:21:04 -0800 (PST), Bry Pas <bryanp...@gmail.com> wrote:
>
> >Hello Tandem Experts and Enthusiast,
> >
> > I would like to to ask for your help and ask for guidance on how to reduce the EOF usage for the file we have (it says SQL Base Table). We have a table that monitors its usage and can see that Partition 1 is at 80.4% already, while Partition 2 is 0.2%. The solutions that I have in mind is to delete some of the old datas in the SQL file, not sure though if it will reduce the usage. Comments and suggestions are very appreciated, cheers everyone..
> >
> >ESCRIPTON VOLUME LOCATION EOF STATUS
> >Partition 0 $DS02.prdaSQL.CMCMBRT 30.7%
> >Partition 1 $DS03.prdaSQL.CMCMBRT 80.4%
> >Partition 2 $DS04.prdaSQL.CMCMBRT 0.2%
> >Partition 3 $DS05.prdaSQL.CMCMBRT 33.8%
> >
> >Index 1 $DS02.prdaSQL 24.2%
> >Index 2 $DS02.prdaSQL 50.2%
> >Index 3 $DS02.prdaSQL 36.5%
> >
> >Below is the fileinfo of the SQL table file:
> >
> >$DS03.PRDASQL.CMCMBRT
> > SQL BASE TABLE
> > CATALOG $DS02.PRDASDX
> > VERSION 2
> > TYPE K
> > FORMAT 1
> > EXT ( 37752 PAGES, 37752 PAGES, MAXEXTENTS 16 )
> > REC 251
> > PACKED REC 250
> > BLOCK 4096
> > KEY ( COLUMN 0, OFFSET 0, LENGTH 10, ASC,
> > COLUMN 1, OFFSET 10, LENGTH 19, ASC,
> > COLUMN 2, OFFSET 29, LENGTH 5, ASC,
> > COLUMN 3, OFFSET 34, LENGTH 2, ASC )
> > INDEX ( 1, $DS02.PRDASQL.CMCMBRX1,
> > COLUMN 11, OFFSET 64, LENGTH 8, ASC,
> > UNIQUE )
> > INDEX ( 2, $DS02.PRDASQL.CMCMBRX2,
> > COLUMN 12, OFFSET 72, LENGTH 17, ASC,
> > COLUMN 13, OFFSET 89, LENGTH 12, ASC,
> > NOT UNIQUE )
> > INDEX ( 3, $DS02.PRDASQL.CMCMBRX3,
> > COLUMN 30, OFFSET 227, LENGTH 24, ASC,
> > NOT UNIQUE )
> > PART ( 0, $DS02, 37752 PAGES, 37752 PAGES, MAXEXTENTS 16, FORMAT 1,
> > ( [ 0, 0, 0, 0, 0, 0, 0, 0, 0, 0 ], [ 0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
> > 0, 0, 0, 0, 0, 0, 0, 0, 0 ], [ 0, 0, 0, 0, 0 ], [ 0, 0 ] ) )
> > PART ( 1, $DS03, 37752 PAGES, 37752 PAGES, MAXEXTENTS 16, FORMAT 1,
> > ( "08", "510266020", [ 0, 0, 0, 0, 0 ], [ 0, 0 ] ) )
> > PART ( 2, $DS04, 37752 PAGES, 37752 PAGES, MAXEXTENTS 16, FORMAT 1,
> > ( "08", "592108275", [ 0, 0, 0, 0, 0 ], [ 0, 0 ] ) )
> > PART ( 3, $DS05, 37752 PAGES, 37752 PAGES, MAXEXTENTS 16, FORMAT 1,
> > ( "08", "592108575", [ 0, 0, 0, 0, 0 ], [ 0, 0 ] ) )
> > AUDIT
> > BUFFERED
> > AUDITCOMPRESS
> > OWNER 190,1
> > SECURITY (RWEP): OOOO
> > SECONDARY PARTITION
> > DATA MODIF: 18 Feb 2022, 16:12, OPEN
> > CREATION DATE: 8 Jul 2019, 20:30
> > REDEFINITION DATE: 5 Jun 2014, 19:43
> > LAST OPEN: 17 Feb 2022, 22:42
> > EOF: 993939456 (80.3% USED)
> > EXTENTS ALLOCATED: 13
> > INDEX LEVELS: 3
> Bryan,
>
> Audited SQL/MP tables such as this can have their partitions 'altered' online, which moves rows from one partition to another.
> It reads like whoever defined these partitions either used an old distribution of the data, or took a guess that wasn't quite right.
>
> Look in the SQL/MP Reference manual, in the ALTER TABLE section There are many options here, including ADD , MOVE , REUSE and DROP of a PARTITION.
> You probably need to use MOVE.
>
> These operations all work using a TMF transaction... with corresponding LOCKS on the table data.
> This should be done by someone with good SQL/MP database management experience.
>
> The first step is to determine what the ideal balance of primary key valuse in the table is, and whether the number of partitions is appropriate.
>
> There is a fairly comprehensive description of how to allow this repartitioning to occur while processing continues, in the 'Considerations' portion of that part of the manual.
> You should read that thoroughly and understand the impacts. There are some simple examples at the end of the ALTER TABLE section.
>
> Bill

In regard to your question about whether deleting older data in the table will reduce the EOF, the answer is that doing so might reduce the EOF, but it depends on details of your data. If the older data has key values that make it fall into partition 1 of the table, then deleting that older data will free up space within partition 1. That will not directly reduce the EOF.. However, after the older data has been deleted, you can use the FUP command RELOAD to compact the data that remains in the partition, and that will reduce the EOF.

Both deleting the older data and the RELOAD can be done while the applications that use the table are up and running, so you should not have to disrupt normal operations for this.

Without knowing the characteristics of the data that is stored in that table, I cannot say whether deleting the old data will only be a temporary solution or will result in a long-term balancing of the data across the partitions of the table.

Pardon me if you have already done the following analysis, but let me point it out in case you have not.

The FIRST KEY value for partition 2 of the table looks suspiciously close to the FIRST KEY value for partition 3 of the table. It could be that whoever set up the table originally, either misunderstood how the values of the keys would be distributed, or made some simple error in calculating the value for the FIRST KEY of partition 2. Note the FIRST KEY values:

null
"08", "510266020"
"08", "592108275" <--- this is the suspicious one
"08", "592108575"

If that FIRST KEY value for partition 2 actually should have been more nearly halfway between the value for partition 1 and partition 3, that could easily explain why too many rows have gone into partition 1 and very few have gone into partition 2. Without knowing more about the data being stored in the table, I cannot say for sure that is the reason for the imbalance, but seems like a reasonable point to investigate.

That second column of the key is 19 bytes long, so the FIRST KEY value is specifying only the high-order 9 bytes of the key value. It might be true that taking the simple-minded approach of changing the "592108275" to something like "550000000" (roughly halfway between the FIRST KEY of partition 1 and partition 3 would NOT be the correct choice. It might be that the data is such that a very large portion of the key values do begin with "08", "592108", and so the correct FIRST KEY value for partition 2 should only be a little smaller than what it currently is -- it depends on the distribution of the full key values.

If you do determine that a particular new value for the FIRST KEY value of partition 2 would result it a much more even balance of rows among the partitions, then Bill's suggestion of using the online version of the ALTER TABLE command probably would be a good way to get the change made. There are some restrictions on the changes a single ALTER TABLE command can make, so sometimes a change requires multiple steps. Someone familiar with using ALTER TABLE for these kinds of table adjustments should be involved.

The online version of ALTER TABLE can be performed while the applications that use the table are running normally, so this change also can be done without disrupting normal operations of the system.

Re: SQL Base Table - EOF is almost Full

<ec983910-0eb2-451d-bc79-4acec4d2b256n@googlegroups.com>

 copy mid

https://www.novabbs.com/computers/article-flat.php?id=363&group=comp.sys.tandem#363

 copy link   Newsgroups: comp.sys.tandem
X-Received: by 2002:adf:f007:0:b0:1ed:b04d:4f2d with SMTP id j7-20020adff007000000b001edb04d4f2dmr1610123wro.263.1645611715478;
Wed, 23 Feb 2022 02:21:55 -0800 (PST)
X-Received: by 2002:a25:30d6:0:b0:624:cd97:b907 with SMTP id
w205-20020a2530d6000000b00624cd97b907mr7765790ybw.542.1645611714890; Wed, 23
Feb 2022 02:21:54 -0800 (PST)
Path: i2pn2.org!i2pn.org!weretis.net!feeder8.news.weretis.net!proxad.net!feeder1-2.proxad.net!209.85.128.87.MISMATCH!news-out.google.com!nntp.google.com!postnews.google.com!google-groups.googlegroups.com!not-for-mail
Newsgroups: comp.sys.tandem
Date: Wed, 23 Feb 2022 02:21:54 -0800 (PST)
In-Reply-To: <3s9a1hpmtfmj5nkjmq780u3oia9e7cdrsc@4ax.com>
Injection-Info: google-groups.googlegroups.com; posting-host=175.176.31.18; posting-account=zrnrigoAAABeE5VxiOwZ7Zq0V2u9uEUs
NNTP-Posting-Host: 175.176.31.18
References: <46c8534a-b3da-4fb2-b0f1-0f9c3a0107d3n@googlegroups.com> <3s9a1hpmtfmj5nkjmq780u3oia9e7cdrsc@4ax.com>
User-Agent: G2/1.0
MIME-Version: 1.0
Message-ID: <ec983910-0eb2-451d-bc79-4acec4d2b256n@googlegroups.com>
Subject: Re: SQL Base Table - EOF is almost Full
From: bryanpas...@gmail.com (Bry Pas)
Injection-Date: Wed, 23 Feb 2022 10:21:55 +0000
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
 by: Bry Pas - Wed, 23 Feb 2022 10:21 UTC

> Bryan,
>
> Audited SQL/MP tables such as this can have their partitions 'altered' online, which moves rows from one partition to another.
> It reads like whoever defined these partitions either used an old distribution of the data, or took a guess that wasn't quite right.
>
> Look in the SQL/MP Reference manual, in the ALTER TABLE section There are many options here, including ADD , MOVE , REUSE and DROP of a PARTITION.
> You probably need to use MOVE.
>
> These operations all work using a TMF transaction... with corresponding LOCKS on the table data.
> This should be done by someone with good SQL/MP database management experience.
>
> The first step is to determine what the ideal balance of primary key valuse in the table is, and whether the number of partitions is appropriate.
>
> There is a fairly comprehensive description of how to allow this repartitioning to occur while processing continues, in the 'Considerations' portion of that part of the manual.
> You should read that thoroughly and understand the impacts. There are some simple examples at the end of the ALTER TABLE section.
>
> Bill

Hi Bill,

Thank you for your comment and suggestion. I will forward this with our App team to look and consider your suggestions, I am new to Tandem/NonStop and don't have a knowledge with SQL/MP but I will try to look into the Manuals and see how the ALTER TABLE works. Again thank you, Bill.

Cheers,

Re: SQL Base Table - EOF is almost Full

<sv6gan$g59$1@dont-email.me>

 copy mid

https://www.novabbs.com/computers/article-flat.php?id=367&group=comp.sys.tandem#367

 copy link   Newsgroups: comp.sys.tandem
Path: i2pn2.org!i2pn.org!eternal-september.org!reader02.eternal-september.org!.POSTED!not-for-mail
From: inva...@nowhere.com (JShepherd)
Newsgroups: comp.sys.tandem
Subject: Re: SQL Base Table - EOF is almost Full
Date: Wed, 23 Feb 2022 23:34:48 -0000 (UTC)
Organization: A noiseless patient Spider
Lines: 53
Message-ID: <sv6gan$g59$1@dont-email.me>
References: <46c8534a-b3da-4fb2-b0f1-0f9c3a0107d3n@googlegroups.com> <3s9a1hpmtfmj5nkjmq780u3oia9e7cdrsc@4ax.com> <ec983910-0eb2-451d-bc79-4acec4d2b256n@googlegroups.com>
Mime-Version: 1.0
Content-Type: Text/Plain; charset=US-ASCII
Injection-Date: Wed, 23 Feb 2022 23:34:48 -0000 (UTC)
Injection-Info: reader02.eternal-september.org; posting-host="691fb897f4ea459f9fc91a33b0c06e4a";
logging-data="16553"; mail-complaints-to="abuse@eternal-september.org"; posting-account="U2FsdGVkX1/fndQcOwCWGwt66JDhnxC06MZV+pw9mog="
Cancel-Lock: sha1:UWIryXWtYe43HmSqEIFGI+grXs8=
 by: JShepherd - Wed, 23 Feb 2022 23:34 UTC

In article <ec983910-0eb2-451d-bc79-4acec4d2b256n@googlegroups.com>,
bryanpascua88@gmail.com says...
>
>> Bryan,=20
>>=20
>> Audited SQL/MP tables such as this can have their partitions 'altered' on=
>line, which moves rows from one partition to another.=20
>> It reads like whoever defined these partitions either used an old distrib=
>ution of the data, or took a guess that wasn't quite right.=20
>>=20
>> Look in the SQL/MP Reference manual, in the ALTER TABLE section There are=
> many options here, including ADD , MOVE , REUSE and DROP of a PARTITION.=
>=20
>> You probably need to use MOVE.=20
>>=20
>> These operations all work using a TMF transaction... with corresponding L=
>OCKS on the table data.=20
>> This should be done by someone with good SQL/MP database management exper=
>ience.=20
>>=20
>> The first step is to determine what the ideal balance of primary key valu=
>se in the table is, and whether the number of partitions is appropriate.=20
>>=20
>> There is a fairly comprehensive description of how to allow this repartit=
>ioning to occur while processing continues, in the 'Considerations' portion=
> of that part of the manual.=20
>> You should read that thoroughly and understand the impacts. There are som=
>e simple examples at the end of the ALTER TABLE section.=20
>>=20
>> Bill
>
>Hi Bill,
>
>Thank you for your comment and suggestion. I will forward this with our App=
> team to look and consider your suggestions, I am new to Tandem/NonStop and=
> don't have a knowledge with SQL/MP but I will try to look into the Manuals=
> and see how the ALTER TABLE works. Again thank you, Bill.
>
>Cheers,

Are you looking to re-partition the table for a better distribution
of rows or simply increase the size of each partition ?

Your MaxExtents is only 16 currently

-- set maxextents larger on a single partitions
alter table <partition name> partonly maxextents <nn>;

-- set maxextents larger on all partitions
alter table <table> maxextents <nn>;

Re: SQL Base Table - EOF is almost Full

<c4b6e88d-de2c-4f2a-afd2-37cda954c0f4n@googlegroups.com>

 copy mid

https://www.novabbs.com/computers/article-flat.php?id=371&group=comp.sys.tandem#371

 copy link   Newsgroups: comp.sys.tandem
X-Received: by 2002:adf:82b4:0:b0:1ed:d109:bdf6 with SMTP id 49-20020adf82b4000000b001edd109bdf6mr3516293wrc.441.1645735014103;
Thu, 24 Feb 2022 12:36:54 -0800 (PST)
X-Received: by 2002:a81:1cc5:0:b0:2d6:17ad:11ea with SMTP id
c188-20020a811cc5000000b002d617ad11eamr4265014ywc.43.1645735013764; Thu, 24
Feb 2022 12:36:53 -0800 (PST)
Path: i2pn2.org!i2pn.org!weretis.net!feeder8.news.weretis.net!proxad.net!feeder1-2.proxad.net!209.85.128.88.MISMATCH!news-out.google.com!nntp.google.com!postnews.google.com!google-groups.googlegroups.com!not-for-mail
Newsgroups: comp.sys.tandem
Date: Thu, 24 Feb 2022 12:36:53 -0800 (PST)
In-Reply-To: <sv6gan$g59$1@dont-email.me>
Injection-Info: google-groups.googlegroups.com; posting-host=187.157.139.174; posting-account=6VebZwoAAAAgrpUtsowyjrKRLNlqxnXo
NNTP-Posting-Host: 187.157.139.174
References: <46c8534a-b3da-4fb2-b0f1-0f9c3a0107d3n@googlegroups.com>
<3s9a1hpmtfmj5nkjmq780u3oia9e7cdrsc@4ax.com> <ec983910-0eb2-451d-bc79-4acec4d2b256n@googlegroups.com>
<sv6gan$g59$1@dont-email.me>
User-Agent: G2/1.0
MIME-Version: 1.0
Message-ID: <c4b6e88d-de2c-4f2a-afd2-37cda954c0f4n@googlegroups.com>
Subject: Re: SQL Base Table - EOF is almost Full
From: rsbec...@nexbridge.com (Randall)
Injection-Date: Thu, 24 Feb 2022 20:36:53 +0000
Content-Type: text/plain; charset="UTF-8"
 by: Randall - Thu, 24 Feb 2022 20:36 UTC

On Wednesday, February 23, 2022 at 6:34:50 p.m. UTC-5, JShepherd wrote:
> In article <ec983910-0eb2-451d...@googlegroups.com>,
> bryanp...@gmail.com says...
> >
> >> Bryan,=20
> >>=20
> >> Audited SQL/MP tables such as this can have their partitions 'altered' on=
> >line, which moves rows from one partition to another.=20
> >> It reads like whoever defined these partitions either used an old distrib=
> >ution of the data, or took a guess that wasn't quite right.=20
> >>=20
> >> Look in the SQL/MP Reference manual, in the ALTER TABLE section There are=
> > many options here, including ADD , MOVE , REUSE and DROP of a PARTITION.=
> >=20
> >> You probably need to use MOVE.=20
> >>=20
> >> These operations all work using a TMF transaction... with corresponding L=
> >OCKS on the table data.=20
> >> This should be done by someone with good SQL/MP database management exper=
> >ience.=20
> >>=20
> >> The first step is to determine what the ideal balance of primary key valu=
> >se in the table is, and whether the number of partitions is appropriate.=20
> >>=20
> >> There is a fairly comprehensive description of how to allow this repartit=
> >ioning to occur while processing continues, in the 'Considerations' portion=
> > of that part of the manual.=20
> >> You should read that thoroughly and understand the impacts. There are som=
> >e simple examples at the end of the ALTER TABLE section.=20
> >>=20
> >> Bill
> >
> >Hi Bill,
> >
> >Thank you for your comment and suggestion. I will forward this with our App=
> > team to look and consider your suggestions, I am new to Tandem/NonStop and=
> > don't have a knowledge with SQL/MP but I will try to look into the Manuals=
> > and see how the ALTER TABLE works. Again thank you, Bill.
> >
> >Cheers,
> Are you looking to re-partition the table for a better distribution
> of rows or simply increase the size of each partition ?
>
> Your MaxExtents is only 16 currently
>
> -- set maxextents larger on a single partitions
> alter table <partition name> partonly maxextents <nn>;
>
> -- set maxextents larger on all partitions
> alter table <table> maxextents <nn>;

If there is a lot of dead space in the table (see DSAP), you can look into FUP RELOAD also.

Re: SQL Base Table - EOF is almost Full

<dcad1308-4a68-42b9-ab07-90916b93aa12n@googlegroups.com>

 copy mid

https://www.novabbs.com/computers/article-flat.php?id=373&group=comp.sys.tandem#373

 copy link   Newsgroups: comp.sys.tandem
X-Received: by 2002:ac8:5c43:0:b0:2de:4ac9:6c62 with SMTP id j3-20020ac85c43000000b002de4ac96c62mr15144833qtj.465.1646027164698;
Sun, 27 Feb 2022 21:46:04 -0800 (PST)
X-Received: by 2002:a81:e45:0:b0:2d6:bc2e:3f66 with SMTP id
66-20020a810e45000000b002d6bc2e3f66mr18405750ywo.54.1646027164463; Sun, 27
Feb 2022 21:46:04 -0800 (PST)
Path: i2pn2.org!i2pn.org!weretis.net!feeder6.news.weretis.net!nntp.club.cc.cmu.edu!45.76.7.193.MISMATCH!3.us.feeder.erje.net!2.us.feeder.erje.net!feeder.erje.net!border1.nntp.dca1.giganews.com!nntp.giganews.com!news-out.google.com!nntp.google.com!postnews.google.com!google-groups.googlegroups.com!not-for-mail
Newsgroups: comp.sys.tandem
Date: Sun, 27 Feb 2022 21:46:04 -0800 (PST)
In-Reply-To: <c4b6e88d-de2c-4f2a-afd2-37cda954c0f4n@googlegroups.com>
Injection-Info: google-groups.googlegroups.com; posting-host=2409:4060:1e:745b:a481:857a:10a4:1570;
posting-account=TiK_qAoAAACD1c9hSLTzDB7YOgUB_D1g
NNTP-Posting-Host: 2409:4060:1e:745b:a481:857a:10a4:1570
References: <46c8534a-b3da-4fb2-b0f1-0f9c3a0107d3n@googlegroups.com>
<3s9a1hpmtfmj5nkjmq780u3oia9e7cdrsc@4ax.com> <ec983910-0eb2-451d-bc79-4acec4d2b256n@googlegroups.com>
<sv6gan$g59$1@dont-email.me> <c4b6e88d-de2c-4f2a-afd2-37cda954c0f4n@googlegroups.com>
User-Agent: G2/1.0
MIME-Version: 1.0
Message-ID: <dcad1308-4a68-42b9-ab07-90916b93aa12n@googlegroups.com>
Subject: Re: SQL Base Table - EOF is almost Full
From: indranil...@gmail.com (INDRANIL CHAKRABORTY)
Injection-Date: Mon, 28 Feb 2022 05:46:04 +0000
Content-Type: text/plain; charset="UTF-8"
Lines: 61
 by: INDRANIL CHAKRABORTY - Mon, 28 Feb 2022 05:46 UTC

On Friday, February 25, 2022 at 2:06:55 AM UTC+5:30, Randall wrote:
> On Wednesday, February 23, 2022 at 6:34:50 p.m. UTC-5, JShepherd wrote:
> > In article <ec983910-0eb2-451d...@googlegroups.com>,
> > bryanp...@gmail.com says...
> > >
> > >> Bryan,=20
> > >>=20
> > >> Audited SQL/MP tables such as this can have their partitions 'altered' on=
> > >line, which moves rows from one partition to another.=20
> > >> It reads like whoever defined these partitions either used an old distrib=
> > >ution of the data, or took a guess that wasn't quite right.=20
> > >>=20
> > >> Look in the SQL/MP Reference manual, in the ALTER TABLE section There are=
> > > many options here, including ADD , MOVE , REUSE and DROP of a PARTITION.=
> > >=20
> > >> You probably need to use MOVE.=20
> > >>=20
> > >> These operations all work using a TMF transaction... with corresponding L=
> > >OCKS on the table data.=20
> > >> This should be done by someone with good SQL/MP database management exper=
> > >ience.=20
> > >>=20
> > >> The first step is to determine what the ideal balance of primary key valu=
> > >se in the table is, and whether the number of partitions is appropriate.=20
> > >>=20
> > >> There is a fairly comprehensive description of how to allow this repartit=
> > >ioning to occur while processing continues, in the 'Considerations' portion=
> > > of that part of the manual.=20
> > >> You should read that thoroughly and understand the impacts. There are som=
> > >e simple examples at the end of the ALTER TABLE section.=20
> > >>=20
> > >> Bill
> > >
> > >Hi Bill,
> > >
> > >Thank you for your comment and suggestion. I will forward this with our App=
> > > team to look and consider your suggestions, I am new to Tandem/NonStop and=
> > > don't have a knowledge with SQL/MP but I will try to look into the Manuals=
> > > and see how the ALTER TABLE works. Again thank you, Bill.
> > >
> > >Cheers,
> > Are you looking to re-partition the table for a better distribution
> > of rows or simply increase the size of each partition ?
> >
> > Your MaxExtents is only 16 currently
> >
> > -- set maxextents larger on a single partitions
> > alter table <partition name> partonly maxextents <nn>;
> >
> > -- set maxextents larger on all partitions
> > alter table <table> maxextents <nn>;
> If there is a lot of dead space in the table (see DSAP), you can look into FUP RELOAD also.
Hello Bry,

Please increase the maxextents of $DS03.PRDASQL.CMCMBRT to 25.

SQLCI
ALTER TABLE $DS03.PRDASQL.CMCMBRT PARTONLY 25;

After that, reload the table partition:
SQLCI
FUP RELOAD $DS03.PRDASQL.CMCMBRT,PARTOF $DS02,SLACK 0,DEALLOCATE,RATE 60;

Re: SQL Base Table - EOF is almost Full

<62efec53-3067-422f-b5dc-eff3ea3e78f1n@googlegroups.com>

 copy mid

https://www.novabbs.com/computers/article-flat.php?id=384&group=comp.sys.tandem#384

 copy link   Newsgroups: comp.sys.tandem
X-Received: by 2002:a05:620a:d87:b0:67b:311c:ecbd with SMTP id q7-20020a05620a0d8700b0067b311cecbdmr21092723qkl.146.1647435979993;
Wed, 16 Mar 2022 06:06:19 -0700 (PDT)
X-Received: by 2002:a25:8c90:0:b0:627:eab0:be91 with SMTP id
m16-20020a258c90000000b00627eab0be91mr27504566ybl.260.1647435979744; Wed, 16
Mar 2022 06:06:19 -0700 (PDT)
Path: i2pn2.org!i2pn.org!weretis.net!feeder6.news.weretis.net!news.misty.com!border2.nntp.dca1.giganews.com!nntp.giganews.com!news-out.google.com!nntp.google.com!postnews.google.com!google-groups.googlegroups.com!not-for-mail
Newsgroups: comp.sys.tandem
Date: Wed, 16 Mar 2022 06:06:19 -0700 (PDT)
In-Reply-To: <dcad1308-4a68-42b9-ab07-90916b93aa12n@googlegroups.com>
Injection-Info: google-groups.googlegroups.com; posting-host=112.198.68.182; posting-account=zrnrigoAAABeE5VxiOwZ7Zq0V2u9uEUs
NNTP-Posting-Host: 112.198.68.182
References: <46c8534a-b3da-4fb2-b0f1-0f9c3a0107d3n@googlegroups.com>
<3s9a1hpmtfmj5nkjmq780u3oia9e7cdrsc@4ax.com> <ec983910-0eb2-451d-bc79-4acec4d2b256n@googlegroups.com>
<sv6gan$g59$1@dont-email.me> <c4b6e88d-de2c-4f2a-afd2-37cda954c0f4n@googlegroups.com>
<dcad1308-4a68-42b9-ab07-90916b93aa12n@googlegroups.com>
User-Agent: G2/1.0
MIME-Version: 1.0
Message-ID: <62efec53-3067-422f-b5dc-eff3ea3e78f1n@googlegroups.com>
Subject: Re: SQL Base Table - EOF is almost Full
From: bryanpas...@gmail.com (Bry Pas)
Injection-Date: Wed, 16 Mar 2022 13:06:19 +0000
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
Lines: 9
 by: Bry Pas - Wed, 16 Mar 2022 13:06 UTC

On Monday, February 28, 2022 at 1:46:05 PM UTC+8, indra...@gmail.com wrote:
> On Friday, February 25, 2022 at 2:06:55 AM UTC+5:30, Randall wrote:
> > On Wednesday, February 23, 2022 at 6:34:50 p.m. UTC-5, JShepherd wrote:

Hello All, thank you so much for your feed back. This helps a lot and I appreciate your inputs, the alter table command is a charm and it extends the maxextent of the table, however I would also like to know how can I distribute the data into the other partition (Partition 2 $DS04.prdaSQL.CMCMBRT 0.2%) since this seems to be not in used by the application. Thank you again.

Re: SQL Base Table - EOF is almost Full

<0e1a41e0-066a-4e73-8955-b3166cf2d998n@googlegroups.com>

 copy mid

https://www.novabbs.com/computers/article-flat.php?id=385&group=comp.sys.tandem#385

 copy link   Newsgroups: comp.sys.tandem
X-Received: by 2002:a37:2f04:0:b0:663:397d:7051 with SMTP id v4-20020a372f04000000b00663397d7051mr383266qkh.333.1647447558932;
Wed, 16 Mar 2022 09:19:18 -0700 (PDT)
X-Received: by 2002:a25:500e:0:b0:633:7dc4:a4c2 with SMTP id
e14-20020a25500e000000b006337dc4a4c2mr666558ybb.542.1647447558760; Wed, 16
Mar 2022 09:19:18 -0700 (PDT)
Path: i2pn2.org!i2pn.org!weretis.net!feeder6.news.weretis.net!1.us.feeder.erje.net!feeder.erje.net!border1.nntp.dca1.giganews.com!nntp.giganews.com!news-out.google.com!nntp.google.com!postnews.google.com!google-groups.googlegroups.com!not-for-mail
Newsgroups: comp.sys.tandem
Date: Wed, 16 Mar 2022 09:19:18 -0700 (PDT)
In-Reply-To: <62efec53-3067-422f-b5dc-eff3ea3e78f1n@googlegroups.com>
Injection-Info: google-groups.googlegroups.com; posting-host=98.42.228.104; posting-account=Lua_ZwoAAADivbVXQF8uKE7dZSUNm9vj
NNTP-Posting-Host: 98.42.228.104
References: <46c8534a-b3da-4fb2-b0f1-0f9c3a0107d3n@googlegroups.com>
<3s9a1hpmtfmj5nkjmq780u3oia9e7cdrsc@4ax.com> <ec983910-0eb2-451d-bc79-4acec4d2b256n@googlegroups.com>
<sv6gan$g59$1@dont-email.me> <c4b6e88d-de2c-4f2a-afd2-37cda954c0f4n@googlegroups.com>
<dcad1308-4a68-42b9-ab07-90916b93aa12n@googlegroups.com> <62efec53-3067-422f-b5dc-eff3ea3e78f1n@googlegroups.com>
User-Agent: G2/1.0
MIME-Version: 1.0
Message-ID: <0e1a41e0-066a-4e73-8955-b3166cf2d998n@googlegroups.com>
Subject: Re: SQL Base Table - EOF is almost Full
From: rkd...@gmail.com (Keith Dick)
Injection-Date: Wed, 16 Mar 2022 16:19:18 +0000
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
Lines: 15
 by: Keith Dick - Wed, 16 Mar 2022 16:19 UTC

On Wednesday, March 16, 2022 at 6:06:21 AM UTC-7, Bry Pas wrote:
> On Monday, February 28, 2022 at 1:46:05 PM UTC+8, indra...@gmail.com wrote:
> > On Friday, February 25, 2022 at 2:06:55 AM UTC+5:30, Randall wrote:
> > > On Wednesday, February 23, 2022 at 6:34:50 p.m. UTC-5, JShepherd wrote:
> Hello All, thank you so much for your feed back. This helps a lot and I appreciate your inputs, the alter table command is a charm and it extends the maxextent of the table, however I would also like to know how can I distribute the data into the other partition (Partition 2 $DS04.prdaSQL.CMCMBRT 0.2%) since this seems to be not in used by the application. Thank you again.

In regard to distributing the data more evenly, I discussed that a bit in my earlier reply on February 22. Do you have specific questions about what I suggested in that reply? If so, please clarify your questions..

1
server_pubkey.txt

rocksolid light 0.9.7
clearnet tor