Topic
6 replies Latest Post - ‏2012-04-12T18:34:17Z by SystemAdmin
SystemAdmin
SystemAdmin
253 Posts
ACCEPTED ANSWER

Pinned topic db2 data load issue - unable to extend

‏2012-04-04T13:51:38Z |
I'm having an issue loading data into RDzUT. I have about 6G of exported ixf files that I want to load into my RDzUT database. We trimmed the database down significantly to get to this point. I about 1/3 way through and I'm now getting and extend error. Can anyone point me to instructions for what I would need to do to give the database more space?

IEC070I DSN910.DSNDBD.DSN00064.E362A.I0001.A001,USERCAT.Z111S.DB2V9
DSNP011I -DB9G DSNPXTN0 - MEDIA MANAGER SERVICES 390
ERROR FOR
DSN910.DSNDBC.DSN00064.E362A.I0001.A001
MMRC=8 DSMRC=00D70014
CONNECTION-ID=SERVER, CORRELATION-ID=db2bp.exe,
LUW-ID=C0A80101.F1BE.C95D2B9EF0DB=42
DSNP007I -DB9G DSNPXTN0 - EXTEND FAILED FOR 391
DSN910.DSNDBD.DSN00064.E362A.I0001.A001.
RC=00D70014
CONNECTION-ID=SERVER, CORRELATION-ID=db2bp.exe,
LUW-ID=C0A80101.F1BE.C95D2B9EF0DB=42
DSNT501I -DB9G DSNISUPI RESOURCE UNAVAILABLE 392
CORRELATION-ID=db2bp.exe
CONNECTION-ID=SERVER
LUW-ID=C0A80101.F1BE.C95D2B9EF0DB=42
REASON 00D70014
TYPE 00000200
NAME DSN00064.E362A

Aaron Allsbrook
ClearBlade
Updated on 2012-04-12T18:34:17Z at 2012-04-12T18:34:17Z by SystemAdmin
  • RDzJohn
    RDzJohn
    268 Posts
    ACCEPTED ANSWER

    Re: db2 data load issue - unable to extend

    ‏2012-04-04T14:32:42Z  in response to SystemAdmin
    The information you need to debug this is in the messages you appended. DSNT501I indicates a DB2 resource is unavailable. If you look up this message it indicates a space problem with the resource identified by TYPE = 200. 200 is Table Space. NAME indicates the Table Space name - DSN00064.E362A. To resolve this issue, you will need to increase the DB2 buffers that back this Table space.

    RDzJohn
    • SystemAdmin
      SystemAdmin
      253 Posts
      ACCEPTED ANSWER

      Re: db2 data load issue - unable to extend

      ‏2012-04-05T15:13:47Z  in response to RDzJohn
      Hi John,

      Thanks for the answer but I think it's a little different of an issue. After working with a dba I am being told that this is more likely an issue with the storage group running out of space. It looks like my default storage (SYSDEFLT) group on RDzUT has a VCATNAME of DSN910. The HLQ of DSN910 then shows the volume of SDSDB93. My understanding is that I modify this storage groups to use multiple volumes.

      First, I would like to create and mount a new volume "DATA00" (which I have instructions for in the install the RDzUT guide) Next I need to modify the storage group to rollover into the volume. Are there any instructions for doing this storage group update in RDzUT? Does this sound correct?
      Aaron Allsbrook
      ClearBlade
      • RDzJohn
        RDzJohn
        268 Posts
        ACCEPTED ANSWER

        Re: db2 data load issue - unable to extend

        ‏2012-04-05T15:33:05Z  in response to SystemAdmin
        Adding volumes to your storage group will likely not on its own fix this problem. The specific problem is with not enough space for DSN00064.E362A. If you don't increase the storage size for the tablespace, it won't be able to use the extra volumes. There are any number of ways to fix the problem. One way is to alter the PRIQTY of the failing tablespace, DSN00064.E362A and then run a reorg utility. From the name, it looks like an implicitly created tablespace, where the PRIQTY is very likely too small. SECQTY should be -1.

        As for the specific details of the DB2 commands and jobs required to pull this off, I'm afraid I can't help you with the specifics. If you have DB2 tools installed you might try those. I have also used the Data perspective in RDz to interact with a database definition. DB2 Control Center used to be a great tool for this, but unfortunately it no longer ships with DB2 UDB. You could also issue the commands directly from SPUFI if you can figure out the command syntax. However you choose to alter the definition for your tablespace, make sure you do a reorg after the change.

        RDzJohn
        • SystemAdmin
          SystemAdmin
          253 Posts
          ACCEPTED ANSWER

          Re: db2 data load issue - unable to extend

          ‏2012-04-06T13:31:44Z  in response to RDzJohn
          Hi John,

          I ran an alter on the tablespace for the primary quantity. Looking in SYSTSABLEPART they were both set to -1

          ALTER TABLESPACE DSN00064.E362A PRIQTY 2000

          Within a few seconds of starting the import I am still getting.

          SQL3306N An SQL error "-904" occurred while inserting a row into the table.

          SQL0904N Unsuccessful execution caused by an unavailable resource. Reason
          code: "00D70014", type of resource: "00000200", and resource name:
          "DSN00064.E362A". SQLSTATE=57011

          I have access to db2 connect and Rational data tools for working with the database. Did I make the change you were expecting? Is there something else I should be investigating?

          Aaron Allsbrook
          ClearBlade
          • RDzJohn
            RDzJohn
            268 Posts
            ACCEPTED ANSWER

            Re: db2 data load issue - unable to extend

            ‏2012-04-06T13:47:59Z  in response to SystemAdmin
            Ok, Aaron. You got me. This error does indicate a problem with resources necessary to extend the storage group. If you add a volume or two you should be ok. Issue this command to add a volume to your storage group.

            ALTER STOGROUP SYSDEFLT ADD VOLUMES (DATA00);

            Alternatively, (and perhaps a better practice,) define a new storage group for your tables and tablespaces and specify the volumes you want to use there. I usually leave the defaults alone.

            RDzJohn
            • SystemAdmin
              SystemAdmin
              253 Posts
              ACCEPTED ANSWER

              Re: db2 data load issue - unable to extend

              ‏2012-04-12T18:34:17Z  in response to RDzJohn
              Thanks John, I think we did it

              Since I tend to forget how I solved my own problems, here are my overly verbose steps (put together with information from the Configuration guide and Johns help here on the forums)

              Step 1. Stop RDzUT -
              - from the zOS console run s shutdb
              - press and refresh with PF11 to watch the jobs get shutdown
              Step 2. Create a new volume on linux using the command
              - in linux command shell run "alcckd /home/ibmsys1/z1090/disks/DATA00 –d3390-9"
              - in linux modify the file aprof11s by adding the entry at the bottom for
              "device 0ab3 3390 3990 /home/ibmsys1/z1090/disks/DATA00"
              - validate with the linux command
              "awsckmap /home/ibmsys1/z/aprof11s"
              Step 3. Start RDzUT
              - from the linux shell execute ./runzpdt (There should be a message about ab3 being offline in the console)
              Step 4. Bring the disk online
              - in the zOS console enter "V AB3,OFFLINE
              - Logon onto RDzUT and create a JCL or modify the one most likely in the PDS IBMUSER.CNTL to do
              //IBMUSERA JOB (ACCT),MSGCLASS=H,NOTIFY=&SYSUID.
              //*-----------------------------------------------------
              //*-----------------------------------------------------
              //* MOD-3: VTOC(0,1,974) INDEX(65,0,50)
              //* MOD-9: VTOC(0,1,2939) INDEX(196,0,150)
              //FORMAT EXEC PGM=ICKDSF
              //SYSPRINT DD SYSOUT=*
              //SYSIN DD *
              INIT UNIT(AB3) NOVALIDATE NVFY VOLID(DATA00) PURGE -
              VTOC(0,1,2939) INDEX(196,0,150)
              /*
              - In the zOS console enter the response R xx,U where xx is the id of the zOS notification probably 01
              - Validate the job returned with a 0 code
              - In the zOS console enter the command V AB3,ONLINE
              - Catalog the volume by logging on and create a JCL or modify the one most likely in the PDS IBMUSER.CNTL to do
              //IBMUSERB JOB (ACCT),MSGCLASS=H,NOTIFY=&SYSUID.
              //*----------------------------------------------------
              //DEFCAT EXEC PGM=IDCAMS,REGION=0M
              //SYSPRINT DD SYSOUT=*
              //SYSIN DD *
              DEFINE USERCATALOG (NAME(USERCAT.VDATA00) ICFCATALOG -
              CYLINDERS(10 5) VOLUME(DATA00) )
              /*

              Step 5. Expand the storage group
              - Connect to the database with a tool like db2 connect.
              - Using the configuration assistant create a connection to the zOS database.
              - default port is 5025
              - default location is Dallas9
              - open a db2 command prompt and connect to DALLAS9
              - run the statement
              "ALTER STOGROUP SYSDEFLT ADD VOLUMES (DATA00);"

              Results - A new volume is online named DATA00 and made it available for the default db2 storage group to use as space to expand table data into.
              Aaron Allsbrook
              ClearBlade