Results 1 to 7 of 7
  1. #1
    Join Date
    Nov 2004
    Posts
    54

    Unanswered: DB2 Connect on AIX & DB2 9.5 for Z/OS - Strange behavior with a query & insert....

    I've got a Windows server with the Db2 clien software installed which has been configured to point to a DB2 Connect server which runs on AIX & that points to a few DB2 9.5 for Z/OS susbystems on our mainframe.

    For the past year, I have been running on one subsystem and everything works great. I can run reports, run queries, inserts etc... All good.

    About 3 weeks ago I was moved to a different subsystem and that is where the 'goofyness' started. If I insert some records (few thousand) and then a minute to two later try to run a report on the Windows machine against the same table I just inserted the records, I get this message:

    Code:
    
    Script error. Scripting Host error. [IBM][CLI Driver][DB2] SQL0904N  Unsuccessful execution caused by an unavailable resource.  Reason code: "00C200E1", type of resource: "00000220", and resource name: "TOTV.DSNDBC.OTD010.OTX00201.I0001.A001".  SQLSTATE=57011
    However, if I perform the same inserts on the Windows server and then go into SPUFI to perform a select or insert on that same table, I get good results right away without any errors.

    Through a few google searches, I found out what a "200" resource type is. Does anyone have any clues where to start with this? I have a few DBAs which are stumped.

  2. #2
    Join Date
    Nov 2004
    Posts
    54
    Not quite sure what this means. I'm thinking I will pass it along to the DBAs too.


    Help - IBM

  3. #3
    Join Date
    Apr 2005
    Location
    USA
    Posts
    130
    Looks like there is an error on table space that exist on mainframes. Check with your DBA to see the current status of table space. Usually 904 error code due to unavaliable resource due to space limitations etc.

    TOTV.DSNDBC.OTD010.OTX00201.I0001.A001

  4. #4
    Join Date
    Nov 2004
    Posts
    54
    If it were out of space, would it then make sense that if I let the DB "sit" and do nothing for a while, that I would then be able to insert more records? In the PC world, being out of space means you are out of space.

    Just a thought.

  5. #5
    Join Date
    May 2010
    Location
    India
    Posts
    84
    Provided Answers: 2
    There might be some change in commit settings when you moved to new windows server. Can you try changing isolation level in SPUFI to CS or RR and trying running the query after inserts? If you get similar error, it will confirm that problem is with commit settings on new machine

  6. #6
    Join Date
    Nov 2004
    Posts
    54
    Good points. I did add a bunch of commits to my inserts and the DBAs saw the logfile usage drop dramatically. Apparently I was filling up the logs and causing a lock something (some technical DB2 term - heh). So, that eased things a bunch.

    I just had one of our scheulers poke around on the terminal to see if he saw anything out of order. He did mention that the DB2 started task was using a bunch of tapes at the time I was doing inserts.

    On Sunday the DBAs also have scheduled a change to increase the logfle allocation & tablespace allocation to 300x/original size (our default install). Kinda funny how this DB2 instance was never used for over 2yrs and then I come along.

    Quote Originally Posted by stiruvee View Post
    There might be some change in commit settings when you moved to new windows server. Can you try changing isolation level in SPUFI to CS or RR and trying running the query after inserts? If you get similar error, it will confirm that problem is with commit settings on new machine

  7. #7
    Join Date
    Nov 2004
    Posts
    54
    9.9999% sure the problem was fixed. DBA increased the logsize and the errors have gone away.

    WOO!

    Apparently, the logs ran out of DASD space and were programmed to roll over to tape. The tape was a bit slower and causes timeouts/errors.

    WHEW!

    Quote Originally Posted by goldfishhh View Post
    Good points. I did add a bunch of commits to my inserts and the DBAs saw the logfile usage drop dramatically. Apparently I was filling up the logs and causing a lock something (some technical DB2 term - heh). So, that eased things a bunch.

    I just had one of our scheulers poke around on the terminal to see if he saw anything out of order. He did mention that the DB2 started task was using a bunch of tapes at the time I was doing inserts.

    On Sunday the DBAs also have scheduled a change to increase the logfle allocation & tablespace allocation to 300x/original size (our default install). Kinda funny how this DB2 instance was never used for over 2yrs and then I come along.

Posting Permissions

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