[SOLVED] Using REPEAT loop to do SQL Insert into DB2 on zOS
See fex below.
The fex below only inserts a single row (first row) into the database table. There are 7200 or so rows in the ALPHA file that I want to insert. It seems like the REPEAT is working as the TYPE returns 10 rows as stated in the REPEAT.... but the INSERT only fires a single time?????
Any guidance would be greatly appreciated.
Thanks and Regards, John Anderson
APP HOLD etga TABLE FILE HOLD_EMVISION_APPS2
SUM APP_NO BY APP_NO NOPRINT HEADING "" FOOTING "" ON TABLE SET PAGE-NUM OFF ON TABLE NOTOTAL ON TABLE HOLD AS SAMTEST_ALPH FORMAT ALPHA -*ON TABLE PCHOLD FORMAT HTML ON TABLE SET HTMLCSS ON ON TABLE SET STYLE * UNITS=IN, SQUEEZE=ON, ORIENTATION=PORTRAIT, $ TYPE=REPORT, GRID=OFF, FONT='TIMES NEW ROMAN', SIZE=10, $ ENDSTYLE END
-SET &&FILENAME='"/opt/webfocus/ibi/apps/etga' || '/samtest_alph.ftm"'; FILEDEF SAMTEST_ALPH DISK &&FILENAME -RUN
-*DELETE FROM AUPPR001.TENVXRF; SQL COMMIT; -ENDLOOP -RUN
Any guidance on how to have the INSERT work for all 7200 rowsThis message has been edited. Last edited by: Kerry,
7.6.10 windows AIX UDB SQL Server
September 29, 2010, 06:10 PM
Waz
I think you need to add a END after each SQL statement.
SQL DB2
INSERT INTO AUPPR001.TENVXRF values ('&CODE.EVAL');
END
-*DELETE FROM AUPPR001.TENVXRF;
SQL DB2
SQL COMMIT;
END
Waz...
Prod:
WebFOCUS 7.6.10/8.1.04
Upgrade:
WebFOCUS 8.2.07
OS:
Linux
Outputs:
HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!
September 30, 2010, 12:08 AM
Francis Mariani
I would create a file that contains the 7200 insert statements and then include that file - I think it would be more efficient this way.
-SET &LIMIT=&LINES;
FILEDEF SQLCODE DISK SQLCODE.FEX
-RUN
-REPEAT ENDLOOP &LIMIT TIMES;
-READ SAMTEST_ALPH NOCLOSE &CODE.9.
-WRITE SQLCODE NOCLOSE INSERT INTO AUPPR001.TENVXRF values ('&CODE');
-ENDLOOP
ENGINE DB2 SET DEFAULT_CONNECTION DSN1
-RUN
SQL DB2
-INCLUDE SQLCODE
END
-RUN
(Try it with 10 first!)
Francis
Give me code, or give me retirement. In FOCUS since 1991
Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
September 30, 2010, 12:18 AM
Waz
To be honest, I would use MODIFY.
Waz...
Prod:
WebFOCUS 7.6.10/8.1.04
Upgrade:
WebFOCUS 8.2.07
OS:
Linux
Outputs:
HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!
September 30, 2010, 09:18 AM
jgelona
The first thing I would do is get rid of all the stuff you don't need. If I was doing this, my fex would look like this:
APP HOLD etga
TABLE FILE HOLD_EMVISION_APPS2
BY APP_NO
ON TABLE SET HOLDFORMAT ALPHA
ON TABLE SAVE
END
-RUN
-SET &LIMIT=&LINES;
-*
-REPEAT ENDLOOP &LIMIT TIMES;
-READ SAVE NOCLOSE &CODE.9.
-TYPE &CODE
ENGINE DB2 SET DEFAULT_CONNECTION DSN1
SQL DB2
INSERT INTO AUPPR001.TENVXRF values ('&CODE.EVAL');
SQL COMMIT;
END
-RUN
-ENDLOOP
Rule #1. &LINES contains a value until the next command is run. Therefore, in this case, there must be a -RUN immediately after the END statement associated with the TABLE command and the -SET &LIMIT should immediated follow that. The way your fex is coded, the you have a FILEDEF being executed before the -SET &LIMIT. The FILEDEF will set &LINES back to 0. I would also put the -RUN inside the -ENDLOOP. As Waz suggested, you need an END after the COMMIT.
The only other thing to worry about is trailing blanks in &CODE. If all the values have 9 characters then there is nothing to worry about. If not, then you may want to use the TRUNCATE function after the -READ for &CODE. Something like this.
-SET &CODE=TRUNCATE(&CODE);
Be sure to use -SET &ECHO=ALL; and -SET &STACK=OFF; while testing. This will let you see exactly what is being generated.
Hope this helps.
In FOCUS since 1985. Prod WF 8.0.08 (z90/Suse Linux) DB (Oracle 11g), Self Serv, Report Caster, WebServer Intel/Linux.
September 30, 2010, 09:50 AM
Francis Mariani
I would NOT set the ENGINE and do the COMMIT "7200 or so" times...
MODIFY generates the same SQL INSERT statements...
Francis
Give me code, or give me retirement. In FOCUS since 1991
Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
September 30, 2010, 11:25 AM
John A
Thanks all. I added the END after each SQL and it worked perfectly.
I am going to explore the MODIFY as it may be a better option...
Again, thanks all.
jpa
7.6.10 windows AIX UDB SQL Server
October 01, 2010, 09:25 AM
jgelona
"MODIFY generates the same SQL INSERT statements..."
That is not exactly true.
MODIFY will generate the same SQL INSERT only if one uses the SQL SET LOADONLY in the MODIFY.
If SQL SET LOADONLY is not used, then MODIFY generates a SELECT for the key to see if it exists before generating the INSERT.
Also, depending on how your masters are defined, with MODIFY, VARCHAR fields will have trailing blanks.
In FOCUS since 1985. Prod WF 8.0.08 (z90/Suse Linux) DB (Oracle 11g), Self Serv, Report Caster, WebServer Intel/Linux.
December 28, 2012, 02:47 PM
SJL123
Just an FYI, I added the SQL SET LOADONLY and SQL SET INSERTSIZE 100 to a modify statement of mine and I was able to populate a table in Oracle with almost 2 Million records, 60 columns wide in less than 10 minutes, it used to take over night. Major improvement! Here's my code:
TABLE FILE MYFILE PRINT * ON TABLE HOLD AS MYHOLDFILE END -RUN
MODIFY FILE ORACLETABLE SQL SET LOADONLY SQL SET INSERTSIZE 100 FIXFORM FROM MYHOLDFILE DATA ON MYHOLDFILE END