Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    May 2003
    Posts
    11

    Unanswered: Tablespace, Load, Full filesystem...

    Hello-

    I am trying to use LOAD as part of an Extract-Load-Transform scheme of moving data and tokenizing strings. I am filling up my TEMPSPACE1 tablespace, according to db2diag.log. I am not actually filling the disk, as I have over a gig free when it halts. How can I fix this? I searched for what sounds like a straightforward question but found myself burried by PCTFREE and Buffer pools with partitions and other complicated terminology.

    I am on AIX 4.3.3 with DB2 8.1 EEE. My ELT scheme works like this: Extract data from raw files into Tabular files, LOAD REPLACE the tab files into temporary tables, Transform string values from the temp tables into tokenized tables and finally LOAD (with CURSORs) the data from the temp tables to real tables. I fill the tablespace during the first wave of tokenizing, which is done with a cursors and selects.

    Thank you very much-

    Mike

  2. #2
    Join Date
    Mar 2003
    Posts
    343
    Pardon my ignorance, but what is a tokenized table?

  3. #3
    Join Date
    May 2003
    Posts
    369

    Re: Tablespace, Load, Full filesystem...

    Have you run the followingb checks:

    1. review latest entries in the db2diag.log file

    2. check df -k to see if AIX/UNIX filesystems are full

    3. Are you loading the data into SMS container based tablespaces? If so beware of the normal 2Gb limit on SMS container sizes. You may be forced to create a new filesystem that has been enabled for large file sizes and to do a redirected restore to refine your SMS based tablespaces if this is indeed the case. We ran into this problem recently and it was not pretty.

    4. Are you running autoloader or load command scripts?

    Hope this helps

    Scott

  4. #4
    Join Date
    Apr 2003
    Posts
    191

    Re: Tablespace, Load, Full filesystem...

    Hi Mike,

    sounds like a SQL problem to me, if the problems surface along with the tokenizing.

    If so, please check for cartesian products in your token select statements.

    Furthermore, you can try to make smaller transactions. After all cursor + select should enable you to do just that.

    Third, what exactly is the relevant output from db2diag.log?

    Johann

    Originally posted by mikeatrpi
    Hello-

    I am trying to use LOAD as part of an Extract-Load-Transform scheme of moving data and tokenizing strings. I am filling up my TEMPSPACE1 tablespace, according to db2diag.log. I am not actually filling the disk, as I have over a gig free when it halts. How can I fix this? I searched for what sounds like a straightforward question but found myself burried by PCTFREE and Buffer pools with partitions and other complicated terminology.

    I am on AIX 4.3.3 with DB2 8.1 EEE. My ELT scheme works like this: Extract data from raw files into Tabular files, LOAD REPLACE the tab files into temporary tables, Transform string values from the temp tables into tokenized tables and finally LOAD (with CURSORs) the data from the temp tables to real tables. I fill the tablespace during the first wave of tokenizing, which is done with a cursors and selects.

    Thank you very much-

    Mike

  5. #5
    Join Date
    May 2003
    Posts
    11
    cchattoraj:

    My tokenized table just matches long varchar() names with short integer ID#'s. I then store the ID# in other tables as a foreign key to the tokenized lookup table. I therefore am saving storage space. I thought I was doing a level of normalization but was told this is correctly called tokenization.

    Johann / mixxalot:

    db2diag shows this:
    2003-07-07-15.18.05.861587 Instance:db2inst Node:000
    PID:26962(db2pclnr 0) TID:1 Appid:none
    buffer pool services sqlbClnrAsyncWriteCompletion Probe:0

    ADM6017E The table space "TEMPSPACE1" (ID "1") is full. Detected on container
    "/data1/db2inst/db2inst/NODE0000/SQL00001/SQLT0001.0" (ID "0"). The underlying
    file system is full or the maximum allowed space usage for the file system has
    been reached. It is also possible that there are user limits in place with
    respect to maximum file size and these limits have been reached.

    2003-07-07-15.18.05.879172 Instance:db2inst Node:000
    PID:44902(db2lrid 0) TID:1 Appid:*LOCAL.db2inst.0CE0B7185328
    buffer pool services sqlbWritePageToDisk Probe:20 Database:MIKE

    ADM6017E The table space "TEMPSPACE1" (ID "1") is full. Detected on container
    "/data1/db2inst/db2inst/NODE0000/SQL00001/SQLT0001.0" (ID "0"). The underlying
    file system is full or the maximum allowed space usage for the file system has
    been reached. It is also possible that there are user limits in place with
    respect to maximum file size and these limits have been reached.
    df -k (Only the relevant partition shown):
    Filesystem 1024-blocks Free %Used Iused %Iused Mounted on
    /dev/lv00 4014080 2271192 44% 907 1% /data1
    How do I know if I am using SMS table spaces?

    I am writing my own load scripts. These are the lines from the script where the tablespace fills up:

    db2 "DECLARE mycurs CURSOR FOR \
    SELECT row_number() over() + COALESCE((SELECT MAX(iid) FROM lookup),0) AS iid, name \
    FROM ( \
    SELECT DISTINCT name \
    FROM table_temp \
    WHERE name NOT IN (SELECT name FROM lookup) \
    ) as SS"
    db2 "LOAD FROM mycurs OF CURSOR INSERT INTO lookup (iid, name) \
    ALLOW READ ACCESS"
    db2 "commit work"
    Thank you very much for the advice so far!

  6. #6
    Join Date
    Apr 2003
    Posts
    191
    Hi,

    so the DISTINCT and the rownumber() part enforce some large ordering operations. You need to check ulimit for the instance owner. You also may need to move the tempspace to a large file enabled file system. Or you can divide your load into smaller chunks, with SQL.

    By the way, do you really need rownumber() and DISTINCT? Have a try without these things and see if it still spills the temp space.

    Johann

    Originally posted by mikeatrpi
    cchattoraj:

    My tokenized table just matches long varchar() names with short integer ID#'s. I then store the ID# in other tables as a foreign key to the tokenized lookup table. I therefore am saving storage space. I thought I was doing a level of normalization but was told this is correctly called tokenization.

    Johann / mixxalot:

    db2diag shows this:


    df -k (Only the relevant partition shown):


    How do I know if I am using SMS table spaces?

    I am writing my own load scripts. These are the lines from the script where the tablespace fills up:



    Thank you very much for the advice so far!

  7. #7
    Join Date
    May 2003
    Posts
    11
    Ugh... it turns out the file system is actually filling up. It stops the load when there are 30 megs free. I moved the temporary table space directory, SQLT0001.0, to a different disk via softlinks and am trying again. Thanks everyone.

  8. #8
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    I think you can use db2relocatedb rather than using softlinks

    Cheers

    Sathyaram
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  9. #9
    Join Date
    Mar 2003
    Posts
    343
    The tempspace is under the database directory and it is SMS. Create a second system tempspace on different disk and drop the default tempspace.

    Also, run a

    ulimit -a

    Part of the o/p should look like

    time(seconds) unlimited
    file(blocks) unlimited <<--
    data(kbytes) 234496
    stack(kbytes) 32768
    memory(kbytes) unlimited

    File(blocks) I think is what the issue is. For tempspace to grow, it needs to be unlimited.

  10. #10
    Join Date
    May 2003
    Posts
    369

    redirected restore or db2relocatedb

    I would have your Unix/AIX admin create another filesystem that is large file size enabled. Check unlimit then perform a redirected restore to migrate the full SMS container based tablespaces to the new filesystem or use the db2relocatedb command. Note you may also do an import/export to move the tablespaces or use load/autoloader to do the same thing. Whatever you feel comfortable with. Good luck its a pain we had to do this recently because the previous DBA was unaware of the AIX limits on regular JFS filesystems. However I do think that AIX fixed this problem with file size limits in extended JFS.

  11. #11
    Join Date
    May 2003
    Posts
    11
    $ ulimit -a
    time(seconds) unlimited
    file(blocks) unlimited
    data(kbytes) 131072
    stack(kbytes) 32768
    memory(kbytes) 32768
    coredump(blocks) 2097151
    nofiles(descriptors) 2000
    $ smit...
    File system name /data1
    NEW mount point [/data1]
    SIZE of file system (in 512-byte blocks) [8028160]
    Mount GROUP []
    Mount AUTOMATICALLY at system restart? yes +
    PERMISSIONS read/write +
    Mount OPTIONS [] +
    Start Disk Accounting? no +
    Fragment Size (bytes) 4096
    Number of bytes per inode 4096
    Compression algorithm no
    Large File Enabled true
    Allocation Group Size (MBytes) 64
    I think I'm set on the large filesystem stuff, at least for now. I might need to come back to this thread when I get a larger hard disk drive.

    My SQL query above hangs, or at least goes very very slowly. There are 600K rows in the lookup table, and 700K in the table_temp table. Using nmon I see one of my processors is near 100% User utilization. The disks are barely spinning up. I left it running overnight, about 14 hours so far. Can I figure out if I'm stuck in a lock?

    $ db2 list applications show detail
    shows: UOW Executing
    Thanks. I'm a real newbie to DB2 and I appreciate all of your help.
    Last edited by mikeatrpi; 07-09-03 at 09:36.

  12. #12
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    Sorry ... Now I'm going back to your query ....

    Do you know when the filesystem fills up , is it in the BUILD Phase or the when the data is collected for Loading ?

    In any case, I think the query needs to be a bit more optimized, may be by adding proper indexes on the tables, if you don't have them now and also based on other suggestions above ....

    Also, consider the usage of rownumber() function , especially if you have used similar rownumber functions with other databases .... I think the function in db2 is not the same as it will be in oracle, sybase etc.

    It is a bit strange with all ulimit etc that the you get the filesystem full message ... Have you checked with your Unix Administrator that there is no filesystem in the machine which gets filled up (may be the filesystem holding the load temp files) ?

    You can also consider creating a multi-container tempspace and then drop the current single container tempspace .... (This may 'hide' the problem and get you going)


    To know about the locks, you need to get the snapshot ... Before this you need to switch the monitor switches on (without this step, you will get only limited info)... If this is a dev/test box, I suggest you turn on the switches at the instance level ... If it is a prod box, you may want to switch on monitors only for a single session ...


    To update dbm cfg, use
    db2 update dbm cfg using <parmname1> on <parmname2> on etc

    To get the parmname list do
    db2 get dbm cfg | grep DFT_MON

    and the parms listed are the ones you need to update ...

    If you want monitoring on only at the session level use the command
    UPDATE MONITOR SWITCHES USING {switch-name {ON | OFF} ...}
    [AT NODE node-num | GLOBAL]
    switch-name:
    BUFFERPOOL, LOCK, SORT, STATEMENT, TABLE, UOW

    And once the above is doen

    db2 get snapshot for locks on <dbname>

    (for info do , db2 ? get snapshot )

    Cheers

    Sathyaram





    Cheers

    Sathyaram
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  13. #13
    Join Date
    May 2003
    Posts
    11
    I received some additional help from within my organization. One colleage suggested using IDENTITY columns since I am in DB2 8. This has greatly increased the speed of my load scripts. I will post more details to help others when I am certain this is the right solution.

  14. #14
    Join Date
    May 2003
    Posts
    11
    For the benefit of others, here is what I discovered:

    1) Disk full problem solved by db2stop, move temporary table space files (usually instance/NODExxxx/SQL000xx/SQLT0001.0/*) to a different disk, softlink to the new location, db2start. I didn't try db2relocatedb yet.

    2) Identity columns declared as follows:
    "id integer not null
    GENERATED ALWAYS AS IDENTITY
    (START WITH 1 INCREMENT BY 1
    MINVALUE 1 NO MAXVALUE NO CYCLE NO CACHE ORDER)"

    3) I change the isolation level to UR before running my load shell scripts

    4) My new query to tokenize strings looks like this:
    db2 "DECLARE mycurs CURSOR FOR \
    SELECT DISTINCT t.name \
    FROM table_temp t \
    WHERE NOT EXISTS (SELECT l.name FROM lookup l WHERE l.name = t.name) \
    FOR READ ONLY"
    db2 "LOAD FROM mycurs OF CURSOR INSERT INTO lookup (name) \
    ALLOW READ ACCESS"

    My Estimated cost from dynexpln is 488K, down from 65 Billion with the old query. I have reduced time 75% or more, depending on how full the tables are when these queries are run.

    Thank you all for the help! I hope this information will be of use to someone else trying an ELT (yes, not ETL!!) approach.

  15. #15
    Join Date
    Mar 2005
    Posts
    73
    Mike / Others

    a few questions to the last post.

    1) what is soft link ? how would you make it use by the tablespace.
    2) What was the advantage of changing the isolation level to UR.

    3) what changes did you make to your sql compared to the one which failed earlier .

    thanks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •