Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

Problem with the load utility - Load From Cursor

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
kothamasu

New User


Joined: 28 Jan 2008
Posts: 6
Location: Sandton

PostPosted: Wed Mar 26, 2008 8:53 pm    Post subject: Problem with the load utility - Load From Cursor
Reply with quote

Hi,

I am trying to laod data into a table using Load from cursor.

I am using the below JCL.

//STEP1 EXEC PGM=DSNUTILB,PARM='FDDG,SCOBVER'
//SYSPRINT DD SYSOUT=*
//SORTWK01 DD UNIT=SYSDA,SPACE=(CYL,(500,250))
//SORTWK02 DD UNIT=SYSDA,SPACE=(CYL,(500,250))
//SORTWK03 DD UNIT=SYSDA,SPACE=(CYL,(500,250))
//SORTWK04 DD UNIT=SYSDA,SPACE=(CYL,(500,250))
//SORTWK05 DD UNIT=SYSDA,SPACE=(CYL,(500,250))
//SORTWK06 DD UNIT=SYSDA,SPACE=(CYL,(500,250))
//SYSPUNCH DD DUMMY
//SYSIN DD *
EXEC SQL
DECLARE C1 CURSOR FOR
SELECT * FROM NORD00.T_PLCY_SCOB_VER
WHERE PLCY_ID IN (265943794,731986307)
AND END_DTE_DTS < (CURRENT TIMESTAMP - 14 MONTHS)
END-EXEC;
LOAD FROM C1 OF CURSOR INSERT INTO NORD01.T_PLCY_SCOB_VER;


When I ran this JCL, I am getting the below error message.

DSNUGUTC - OUTPUT START FOR UTILITY, UTILID = SCOBVER
DSNUGTIS - PROCESSING SYSIN AS EBCDIC
DSNUGPSP - EXEC SQL
DSNUGPSP - INSUFFICIENT OPERANDS FOR KEYWORD 'EXEC'
DSNUGPSP - DECLARE C1 CURSOR FOR
DSNUGPSP - SELECT * FROM NORD00.T_PLCY_SCOB_VER
DSNUGPSP - WHERE PLCY_ID IN (265943794,731986307)
DSNUGPSP - AND END_DTE_DTS < (CURRENT TIMESTAMP - 14 MONTHS)
DSNUGPSP - END-EXEC
DSNUGPSP - LOAD FROM C1 OF CURSOR INSERT INTO NORD01.T_PLCY_SCOB_VER
DSNUGBAC - UTILITY EXECUTION TERMINATED, HIGHEST RETURN CODE=8

What could probably be the reason for this?

Thanks,
Kothamasu.
Back to top
View user's profile Send private message

stodolas

Active Member


Joined: 13 Jun 2007
Posts: 632
Location: Wisconsin

PostPosted: Wed Mar 26, 2008 10:47 pm    Post subject:
Reply with quote

Why are you using a cursor? Why not just use this?

Code:

INSERT INTO NORD01.T_PLCY_SCOB_VER         
 (SELECT *
  FROM NORD00.T_PLCY_SCOB_VER   
  WHERE PLCY_ID IN (265943794,731986307)
  AND END_DTE_DTS < (CURRENT TIMESTAMP - 14 MONTHS);                     

COMMIT;                                   
Back to top
View user's profile Send private message
kothamasu

New User


Joined: 28 Jan 2008
Posts: 6
Location: Sandton

PostPosted: Thu Mar 27, 2008 12:52 pm    Post subject:
Reply with quote

I have to load a total of 30 million records. So, to increase the Insert performence, I am trying to use the cursor.
Back to top
View user's profile Send private message
shrivatsa
Warnings : 1

Active User


Joined: 17 Mar 2006
Posts: 171
Location: Bangalore

PostPosted: Thu Mar 27, 2008 1:03 pm    Post subject: Reply to: Problem with the load utility - Load From Cursor
Reply with quote

As stodolas Sugessted use

Code:
INSERT INTO NORD01.T_PLCY_SCOB_VER         
 (SELECT *
  FROM NORD00.T_PLCY_SCOB_VER   
  WHERE PLCY_ID IN (265943794,731986307)
  AND END_DTE_DTS < (CURRENT TIMESTAMP - 14 MONTHS);                     

COMMIT; 


One thing I didn't understand is that why you have used EXEC SQL

I fell that EXEC SQL is used only in EMBEDED PROGRAMING..
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Thu Mar 27, 2008 7:38 pm    Post subject:
Reply with quote

Hello,

Quote:
I have to load a total of 30 million records. So, to increase the Insert performence, I am trying to use the cursor.
If you really want to improve performance, you might look into running this as 2 steps. The first to create a qsam file of the info to be loaded and the second to actually load the data using the load utility.

30million individual inserts is (probably) the highest overhead way to get the data into the table. . .
Back to top
View user's profile Send private message
kothamasu

New User


Joined: 28 Jan 2008
Posts: 6
Location: Sandton

PostPosted: Fri Mar 28, 2008 8:58 pm    Post subject:
Reply with quote

Hi,

I read the concept of loading the table using a Cursor in one of the White Papers. They mentioned like, if we are loading the table using a cursor, the performence can be increased by 20%.

Actually I have to load a total 9 tables which are almost of equal size. That's why I am looking for a better option.

Thanks for the feed back.
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Testing rerad cursor for status with ... John F Dutcher DB2 8 Fri May 19, 2017 9:35 pm
No new posts DB2 - TERMINATE utility vasanthz DB2 5 Mon May 15, 2017 11:49 pm
No new posts Cobol upgrade - source code missing f... gthmrj IBM Tools 1 Wed Apr 26, 2017 6:04 pm
No new posts Copy load modules and alias vasanthz IBM Tools 5 Thu Apr 20, 2017 6:31 am
No new posts HLIST Utility In Batch Virendra Shambharkar TSO/ISPF 4 Fri Apr 07, 2017 3:38 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us