Page 1 of 2 12 LastLast
Results 1 to 15 of 20
  1. #1
    Join Date
    Aug 2012
    Posts
    24

    Unanswered: How do i insert huge count of records from one table to another table in DB2 10

    I have to perform one insert operation from one table to another table.

    But the table is having 7 million records, Which contains duplicate entries.So i have to find the distinct batch no form the table.


    insert into tab1(slno,batchno)
    select slno,a.batchno from
    (select distinct batchno from tab2) a;


    While performing insertion i am getting the below error message.

    UNSUCCESSFUL EXECUTION CAUSED BY AN UNAVAILABLE RESOURCE. REASON 00C90096, TYPE OF RESOURCE 00000302, AND RESOURCE NAME DVA001D3.SVA068D3.X'00004E6C'. SQLCODE=-904, SQLSTATE=57011, DRIVER=4.12.55

    Can anyone please suggest various methods how to achive the same in DB2 z/OS 10.

  2. #2
    Join Date
    Jan 2009
    Location
    Zoetermeer, Holland
    Posts
    746
    Does this SQL work when there is less data? Looks like invalid SQL to me. Where does the 'sino' column come from?
    Somewhere between " too small" and " too large" lies the size that is just right.
    - Scott Hayes

  3. #3
    Join Date
    Aug 2012
    Posts
    24
    In the below query 'slno' is a auto generated sequence number for Tab2.

    select slno,a.batchno from
    (select distinct batchno from tab2) a;

    which result set need to be entered into Tab1 but the main problem is due to huge records fetched to insert into Tab1 resulting the error message.

    Is there any other possible ways(with query) to insert the 7 million records into the tab1.

  4. #4
    Join Date
    Jun 2007
    Location
    germany
    Posts
    155
    what is the resource that is unavailable?
    if you determine that, you may solve your problem.

    why do you have to move 7 million rows (Files have records) with an sql query,
    instead of an unload/load?

    will invalid SQL generate a -904 SQLCODE?
    Dick Brenholtz, Ami in Deutschland

  5. #5
    Join Date
    Aug 2012
    Posts
    24
    Error SQLCODE -904 SQLSTATE 57011
    Explanation: Resource limit exceeded.
    May be allocated row insertion lock exceeded to process the request.



    I have tried using Load with cursor in the below query.

    declare test cursor for select slno1,a.batchno from
    (select distinct batchno from tab2) a;
    load from test of cursor insert into schema1.tab1(slno,batchno);

    ERROR:-

    UNACCEPTABLE SQL STATEMENT. SQLCODE=-84, SQLSTATE=42612, DRIVER=4.12.55
    ILLEGAL SYMBOL "LOAD". SOME SYMBOLS THAT MIGHT BE LEGAL ARE: <ERR_STMT> <WNG_STMT> GET SQL SAVEPOINT HOLD FREE ASSOCIATE. SQLCODE=-104, SQLSTATE=42601, DRIVER=4.12.55


    Can anyone please check and modify the same query?

  6. #6
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    without knowing the db2/z syntax : load from test of cursor
    seems to be luw syntax
    looking at http://www.db2expert.com/downloads/d...OSRefGuide.pdf the syntax is not the same
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

  7. #7
    Join Date
    Aug 2012
    Posts
    24
    I have tried the follow the syntax provided in the below link by doing the modification in my query.

    IBM DB2 9.7 Information Center for Linux, UNIX, and Windows

    Eg:-

    Executing the following CLP commands will load all the data from ABC.TABLE1 into ABC.TABLE2:

    DECLARE mycurs CURSOR FOR SELECT TWO, ONE, THREE FROM abc.table1
    LOAD FROM mycurs OF cursor INSERT INTO abc.table2

    declare test cursor for select slno1,a.batchno from
    (select distinct batchno from tab2) a;
    load from test of cursor insert into schema1.tab1(slno,batchno);

    After executing the same getting the above error as posted earlier.

    Can anyone please have a look and modify my syntax??

  8. #8
    Join Date
    Apr 2012
    Posts
    1,035
    Provided Answers: 18
    Your first post in this thread stated that you are using DB2 v10 on Z/OS.

    Why do you reference the LUW documentation (instead of the DB2 for Z/OS documentation)?

    Maybe you should talk with your DBA for DB2 on Z/OS about using the LOAD utility on that platform.

    The DB2 on Z/os documentation shows samples for LOAD utility control statements:

    DB2 10 - Utilities - Sample LOAD control statements

  9. #9
    Join Date
    Aug 2012
    Posts
    24
    Thanks for the info.

    I have tried the below query to load data using cursor but getting the error message.

    declare test cursor for select slno1,a.batchno from
    (select distinct batchno from tab2) a
    LOAD DATA
    INCURSOR(test)
    REPLACE INTO TABLE schema1.tab1(slno,batchno)
    STATISTICS;

    ERROR:-

    ILLEGAL SYMBOL "LOAD". SOME SYMBOLS THAT MIGHT BE LEGAL ARE:EXCEPT MINUS UNION QUERYNO SKIP FOR OPTIMIZE <END-OF-STATEMENT>.
    SQLCODE=-104, SQLSTATE=42601, DRIVER=4.12.55

  10. #10
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    please read the entries from other users

    are you using db2 luw or Z/os ??
    use the syntax that goes with that target db .......
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

  11. #11
    Join Date
    Jun 2007
    Location
    germany
    Posts
    155
    if you are on Z/os and incapable of executing a batch unload and then load,
    you are going to have to modify your SQL
    to only subselect those that are not already in the target table
    and resort to FETCH FIRST n ROWS,
    and then continually run the SQL in spufi until there are no more to INSERT
    FETCH FIRST 50,000 rows,
    you would only have to run it 140 times.

    or try with qmf for the SELECT,
    export your results
    and then INSERT your exported file.
    Last edited by dbzTHEdinosaur; 09-05-12 at 06:39. Reason: removed sarcasm, added qmf blurb
    Dick Brenholtz, Ami in Deutschland

  12. #12
    Join Date
    Aug 2012
    Posts
    24
    Hi,

    I am able to load 7 million records by using cursor in the below query mentioned in luw enviourment.

    declare test cursor for select slno1,a.batchno from
    (select distinct batchno from tab2) a;
    load from test of cursor insert into schema1.tab1(slno,batchno);

    But can anyone please suggest me how to achieve the same in Z/OS 10 envirnment.

    Can it be achived by using Bulk insert statement?

    Please suggest.

  13. #13
    Join Date
    Aug 2012
    Posts
    24
    Can anyone please suggest various methods to insert 7 million records from one table to another table in Db2 z/os 10.

    As per the earlier post replies,direct insert shows the below query.

    UNSUCCESSFUL EXECUTION CAUSED BY AN UNAVAILABLE RESOURCE. REASON 00C90096, TYPE OF RESOURCE 00000302, AND RESOURCE NAME DVA001D3.SVA068D3.X'00004E6C'. SQLCODE=-904, SQLSTATE=57011, DRIVER=4.12.55

    SQL0904 SQLCODE -904 SQLSTATE 57011

    Explanation: Resource limit exceeded.

    Please suggest.

  14. #14
    Join Date
    Apr 2012
    Posts
    1,035
    Provided Answers: 18
    Contact your DBA for DB2 v10.1 on Z/OS.
    See suggestions here:
    DB2 10 - Updated topic - 00C90096

  15. #15
    Join Date
    Aug 2012
    Posts
    24
    Thanks for the reply.

    As per the error description DBA should increase the row lock limit.

    But is there any other approach to insert the huge data into table.

    Please suggest.

Posting Permissions

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