|
|
| Author |
Message |
kothamasu
New User
Joined: 28 Jan 2008
Posts: 3
Location: Sandton
|
|
|
|
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 |
|
 |
References
|
 Posted: Wed Mar 26, 2008 8:53 pm Post subject: Re: Problem with the load utility - Load From Cursor |
|
|
|
|
stodolas
Senior Member
Joined: 13 Jun 2007
Posts: 646
Location: Wisconsin
|
|
|
|
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 |
|
|
kothamasu
New User
Joined: 28 Jan 2008
Posts: 3
Location: Sandton
|
|
|
|
| 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 |
|
|
shrivatsa
Active User
Joined: 17 Mar 2006
Posts: 157
Location: Pune
|
|
|
|
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 |
|
|
dick scherrer
Global Moderator
Joined: 23 Nov 2006
Posts: 7505
Location: 221 B Baker St
|
|
|
|
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 |
|
|
kothamasu
New User
Joined: 28 Jan 2008
Posts: 3
Location: Sandton
|
|
|
|
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 |
|
|
|
|
|
