|
View previous topic :: View next topic
|
| Author |
Message |
kothamasu
New User
Joined: 28 Jan 2008
Posts: 6
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 |
|
 |
stodolas
Active Member
Joined: 13 Jun 2007
Posts: 632
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: 6
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
Warnings : 1
Active User
Joined: 17 Mar 2006
Posts: 174
Location: Bangalore
|
|
|
|
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
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
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: 6
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 |
|
|
|
|
