Michael Holzer writes..
 
> My ALLBASE LOAD script fails with:
> TABLE space exhausted in DBEFileSet SYSTEM.  (DBERR 2502)
 
> I'm wondering about the required SYSTEM table space in relation
> to the userdata table space. I increased the SYSTEM table space
> 2 times by 50000 pages but always got the same error.
 
> Any ideas what could be the real failure reason?
 
^^^
   The failure is happening during the SORT/MERGE operation
   required for the DISTINCT clause and join op in....
 
     INSERT INTO IWHDBE.ACK_DT_TBL_UPDT
             SELECT DISTINCT
                    ACK_DT_TBL_NEW.ORD_ITEM
             FROM   ACK_DT_TBL_NEW, ACK_DT_TBL
             WHERE  ACK_DT_TBL_NEW.ORD_ITEM =
                    ACK_DT_TBL.ORD_ITEM;
 
   Three ways of getting around this come to mind:
 
   * Use less sort space
 
     The statement above will cause two index scans, a
     merge join op and a distinct op.  By rearranging
     the statement, you will avoid the merge join op thus
     requiring less sort space....as follows
 
      INSERT INTO IWHDBE.ACK_DT_TBL_UPDT
             SELECT DISTINCT
                    A.ORD_ITEM
             FROM   ACK_DT_TBL_NEW A
             WHERE  A.ORD_ITEM IN
                   (SELECT B.ORD_ITEM
                      FROM ACK_DT_TBL B
                     WHERE B.ORD_ITEM=A.ORD_ITEM);
 
     The statement above will cause two index scans and one
     distinct op, meaning that you will probably use just
     TEMPSPACE to complete the query.
 
 
   * Use another DBEFileSet with more space for sorting.
     Do this by setting VAR HPSQLtempdbefs to the
     DBEFileSet of choice before running ISQL.
 
   * Create some TEMPSPACE.
 
   You need to calculate the number of pages needed for tempspace
   or dbefileset. I have assumed the following:
 
   Number of data pages (say 30 rows per page) 15000/30=500
   I do not know how many rows are in ACK_DT_TBL, but according
   to your space stats diagram, there's 1345 pages in the fileset
   so I have to assume that 1345 pages need to be sorted. Of course
   the 1345 is probably on the low side because 2032 rows were
   deleted then replaced with 15032 rows.
 
   You need 3 times this number for the join = 1345*3=4035 pages
 
   Now, 4035 seems to be way under the amount of space you have in your
   SYSTEM FileSet. I say "seems" because your space stats diagram shows
   used to be zero% and this is not possible.  You should "update stats"
   on tables (and system views) in the SYSTEM FileSet to get a better
   picture of things (use one of the 3rd party DBA tools to make your
   life easier here)
 
   I suggest trying the following :
 
     CONNECT TO 'IWHDBE';
     CREATE TEMPSPACE anyname  WITH MAXFILEPAGES = 50000,
            LOCATION ='somegrp.someacct';
     COMMIT WORK;
 
   This does not cause a file to be created
   I have set maxfilepages to 50000 to allow for any other usage in
   your script and inaccuraces in your space stats diagram.
 
   -------
 
   Overall, I think rearrangement of your insert statement is probably
   the best cause of action.
 
 
   Regards
   Mike Whiteley
   Sector 7 Software
   England