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

-*-SET &LIMIT=&LINES;
-REPEAT ENDLOOP 10 TIMES;
-READ SAMTEST_ALPH NOCLOSE &CODE.9.
-TYPE &CODE
ENGINE DB2 SET DEFAULT_CONNECTION DSN1
SQL DB2
INSERT INTO AUPPR001.TENVXRF values ('&CODE.EVAL');

-*DELETE FROM AUPPR001.TENVXRF;
SQL COMMIT;
-ENDLOOP
-RUN

Any guidance on how to have the INSERT work for all 7200 rows

This message has been edited. Last edited by: Kerry,

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

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!)

To be honest, I would use MODIFY.

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.

I would NOT set the ENGINE and do the COMMIT "7200 or so" times...

MODIFY generates the same SQL INSERT statements...

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

"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.

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