SQL CRTDUPOBJ

ekempter

ekempter

Administrator
What is the equivalent of CRTDUPOBJ in OS/400 SQL? The iSeries doesn't appear to support temporary tables (meaning that it won't create the table on the fly (JIT) as in INSERT INTO temp_table 2 SELECT * FORM original_table. Thanks in advance for any assistance.
 
ekempter:

Well, if you are running your SQL statements interactively (meaning from STRSQL from the command line), you can press F13, select 1. Change session attributes and SELECT ouput "3=File" to store the results of your interactive select in a table defined on this screen.

If you want to pass the command in an SQL, you could do the following:

CALL QCMDEXC ("commandstring", LENGTH)

Be sure to use 15,5 formatting for the length.

For example, interactively from STRSQL, you could do the following:

CALL QCMDEXC("wrkactjob",0000000009.00000)

So, you could conceivably do a CRTDUPOBJ in the parameter of CALL QCMDEXC.

Just my two cents worth.

I am sure there is a better way.

Roby
 
Thanks for your response. I am attempting to do this using a RUNSQLSTM. I am hoping that someone will be able to provide an SQL statement to achieve this.
 
You shouldn't have any problem doing this in RUNSQLSTM, either.

Create a source file member such as the following:

*************** Beginning of data ***************
0001.00 CALL QSYS.QCMDEXC('WRKACTJOB',0000000009.00000)
****************** End of data ******************
Then simply run: RUNSQLSTM SRCFILE(lib/file) SRCMBR(mbr)

Should work fine ;-).

Here is a more complex example:

*************** Beginning of data **************************************************
0001.00 CREATE TABLE QTEMP.TEST1 (FIELD1 CHAR(25));
0002.00 INSERT INTO QTEMP.TEST1 SELECT 'GARBAGE' FROM SYSIBM.SYSDUMMY1;
0003.00 CALL QSYS.QCMDEXC('CRTDUPOBJ OBJ(TEST1) FROMLIB(QTEMP) OBJTYPE(*FILE) NEWOBJ(TES
0004.00 T2)',0000000064.00000);
0005.00 INSERT INTO QTEMP.TEST2 SELECT * FROM QTEMP.TEST1;
****************** End of data *****************************************************

And run with....

RUNSQLSTM SRCFILE(NEXGENRW/QTXTSRC) SRCMBR(TEST2) COMMIT(*NONE) NAMING(*SQL) OUTPUT(*PRINT)

Of course, you can use *SYS naming (default) and use the same in your text file.

Hope this helps.

Roby
 
you can try this.

1. create table lib/targettable like lib/sourcetable - This create an empty table
2. insert into lib/targetable select * from lib/sourcetable - This will populate the targettable.
 
Back
Top