IBM i 7.1 brought us the RUNSQL command. This allows me to run SQL statements within the CL.
Prior to IBM i 7.1 I would have to use the RUNSQLSTM command. To use this command I need to have the SQL statement in a source member. The way I knew to run build a SQL statement in CL program was to write it to a source member that could then be used by the RUNSQLSTM command. I always considered it too much work and either used a SQLRPGLE, RPGLE with embedded SQL, or a QMQRY, Query Management Query.
While I do not see the RUNSQL command replacing my use of SQLRPGLE, I do see it eliminating most of my use of QMQRYs.
You cannot execute all SQL statements using the RUNSQL command. Click on the link at the bottom of this post to the IBM website for a list of what statements are supported.
In this first example I want to create an outfile in QTEMP using the CREATE TABLE SQL statement I used in the post Creating a SQL table on the fly:
01 PGM
02 RUNSQL SQL('Drop Table qtemp/temp_table') +
COMMIT(*NONE)
03 MONMSG MSGID(SQL9010)
04 RUNSQL SQL('CREATE TABLE QTEMP/TEMP_TABLE AS +
(SELECT FLD0,FLD1,FLD2 +
FROM TESTPF +
WHERE FLD0 = ''1'') +
WITH DATA') +
COMMIT(*NONE)
05 ENDPGM
|
The first RUNSQL command, line 2, executes the SQL DROP TABLE command to delete the outfile in QTEMP. This also shows that you can use both lower and upper case in the command. I do not want to use commitment control for any of my SQL statements, therefore, I have *NONE in the COMMIT parameter.
If an error is encounter, i.e. the table does not exist, then it is monitored by the use of the MONMSG for the message SQL9010. SQL9010 simply means “Error found in SQL statement".
The second RUNSQL command, line 4, creates the outfile using the CREATE TABLE statement.
It is possible to do database functions too:
01 PGM
02 DCL VAR(&VALUE) TYPE(*CHAR) LEN(10) VALUE('TEST')
03 DCL VAR(&PARM) TYPE(*CHAR) LEN(100)
03 RUNSQL SQL('INSERT INTO MYLIB/TESTPF +
VALUES(''' || &VALUE |< ''', +
123,''ABC'')') +
COMMIT(*NONE)
04 CHGVAR VAR(&PARM) +
VALUE('DELETE FROM MYLIB/TESTPF +
WHERE FLD0 = ''' || &VALUE |< +
''' ')
05 RUNSQL SQL(&PARM) COMMIT(*NONE)
06 ENDPGM
|
Note: The || and |< are the shorthand equivalent of the *CAT and *BCAT functions in CL.
Line 3 shows how I could integrated a variable into the text string in the SQL parameter.
Line 4 and 5 show how I can use a variable in the SQL parameter.
You can learn more about the RUNSQL command on IBM's website here.
While this makes running SQL statements in CL easier I still prefer to use the db2 pase command with QSH. Using that you can run any statement and pipe output from select statements to ifs files.
ReplyDeleteGlad to see SQL is catching on. A few of us have used it since V1R3 of OS/400 back in 1990, but what the heck. Maybe this SQL thing may catch on.
DeleteHi Simon, Greetings of the day.
DeleteI am not able to pass the parameter to the host variable &LIB in below Query.
Please guide me with your valuable inputs on this.
Query:
SELECT * FROM TABLE (QSYS2.OBJECT_STATISTICS('&LIB','FILE')) AS X
Here i am not able to pass the library value into &LIB.
Thanks in advance.
Venu N.
You cannot use the SELECT on its own in RUNSQL. You have to create a table to contain the output.
DeleteRUNSQL
DeleteSQL('CREATE TABLE QTEMP.OUTFILE AS +
(SELECT * FROM TABLE(QSYS2.OBJECT_STATISTICS(+
''' || %TRIMR(&LIB) || ''', +
''FILE'',OBJECT_NAME => ''DEVSRC'')) A) +
WITH DATA') +
COMMIT(*NC) +
NAMING(*SQL)
Thanks for posting. But please be carefull with SQL commands built by concatenation of strings because this makes your code vulnerable for SQL injections.
ReplyDeleteI'm on V6R1 and still using the utility to run SQL in a CL that I picked up from an article in News3X/400 (a long time ago). I called my command RUNSQL with a parm SQLSTM so hopefully only minor changes will be required to make use of the IBM command when we finally upgrade to V7.
ReplyDeleteNice overview, Simon. Thanks for sharing it here. I wasn't aware of the new command but after seeing how it's used I suspect I, too, will find it useful.
ReplyDeleteFYI: RUNSQL is also present in V6R1. It is not shown in the Help Center It was created (on my system) by *IBM on 8/28/12 and so may have crept in with a PTF.
ReplyDeleteI cannot find any reference to it in the IBM Information Center for V6R1. I have assume that you are correct and it crept in with a PTF or a Technical Release update.
DeleteRUNSQL was released for V6.1 in PTF Group SF99601 level 25, (Ref: https://pic.dhe.ibm.com/infocenter/iseries/v6r1m0/topic/rzaq9/rzaq9.pdf p. 32.).
DeleteBtw this manual is a very good foundation to get up-to-date on many small changes and features that came with V6.1
ALERT: Using the RUNSQL command be aware that SQL-string to be executed must not exceed 5000 chars in length.
It sure makes life simpler compared to RUNSQLSTM but it's a pain in a way because of having to escape all the quotes: get ready for constants like q ('''') and rpq ('('''), qcq ('''','''') & etc..
ReplyDeleteCan we use Select statement inside RUNSQL?
ReplyDeleteI can do:
DeleteRUNSQL SQL('insert into qtemp/outfile (select * from mylib/orddtl where ordnbr = ''SK280MM'')') COMMIT(*NONE)
Simon, this may be a silly question, but how do you "send" your table in qtemp to the user? thanks Ann
ReplyDeleteI copy the file from QTEMP to a folder in the IFS.
DeleteThen I could use the SNDSMTPEMM command to email a file from the IFS.
You will find the post here.
A couple observations:
ReplyDelete1) SQL9010 is a generic error message, but it doesn't indicate the root cause of the failure. In this case, the assumption that the root cause is that the table/file does not yet exist may always work. But for other situations when multiple types of errors are possible for the SQL statement being run, the CL program could search messages in program message queues to find the reason for the SQL statement failure. For example a technique such as this could be used to find the SQxxxxx MSGID corresponding to the error :
http://pic.dhe.ibm.com/infocenter/iseries/v6r1m0/index.jsp?topic=/rbam6/rcvmsgpgm.htm
2) For performance, instead of using RUNSQL to always run DROP TABLE, the CHKOBJ CL command could be used to see if the table/file already exists (assuming a table with system name, vs SQL name). If it does exist, and it typically contains only a small number of rows, RUNSQL could run a DELETE statement to clear any existing rows. Reusing the existing table in such cases (minimal no. of rows) should be quicker than always doing a DROP + CREATE. Of course if CHKOBJ determines the table doesn't exist, the CREATE TABLE as shown would be run.
Stupid line of questioning, please pardon my ignorance. I was able to get SQL in CL as of V4R5 and even accepting cl variables as parameters for selecting, I was using the query management package (I think) and some techniques described in a book called "SQL developers guide" by Conte. It seemed to work great for reading from tables, but, if you needed to update or insert into any table, you needed to establish a commitment control (journaling) environment over those files which did work 100% but was a royal pain. (Interactive STRSQL environment handled that for us automatically.) It was fun but I never did any serious production work with it.
ReplyDeleteIt sounds like what is going on above is a whole lot better, I am running 7.1 myself. If I do embed SQL as you all are describing into CL will the O/S automatically manage the commitment control environment for you ? If some one were to cancel the job that does inserts or updates in mid process will the O/S roll back the changes like STRSQL does ?
Again pardon my ignorance .... I just wanted to use this for something important and do not have as much time as I would prefer to find the pitfalls.
Greg, the OS actually does not implicitly manage commitment control anywhere. The same is valid for RUNSQL command as it is for STRSQL, SQL modules, programs, serviceprograms, RUNSQLSTM and whatever else is out there. You have to use the COMMIT parameter right, to make commitment control work.
DeleteTo add to Joachim's reply...
DeleteThe RUNSQL command does contain a COMMIT parameter, which can be used for commitment control. In my examples I chose not to.
Greg, I'm not sure the cause for your commitment control woes with QM - neither the CRTQMQRY nor the STRQMQRY command have a COMMIT parameter that RUNSQLSTM and RUNSQL and maybe STRSQL (I didn't look) have.
DeleteQuery/400 and QM Query don't provide any means to manipulate the data source. Therefore commitment control is not necessary. This is much different with the SQL related commands we are talking about. There is no need for commitment control, if you are using SQL just for querying purposes. Once transactions come in the game, it's a whole different story. (Still you can take your chances with COMMIT(*NONE)!)
DeleteUsing STRSQL you can set the isolation level either when issuing STRSQL or using F13 to set the session properties.
Thank you all so much for the quick responses, much appreciated.
DeleteWhat was happening to me was if I journaled the file I could pass parameters into the QM query that updated or inserted and it ran well. If I was only selecting I did not need to do that. In STRSQL I could update or insert no problem .... if I did that in debug mode maximum message logging you could see the interactive STRSQL job creating and destroying a temporary journal.
I was just wondering what this new technique was in this respect and you all nailed it ... thank you so much again, that obvious in retrospect parameter option. That gives me the extra warm fuzzies to do something important/urgent with it, but, with the normal due diligence in advance of anything new in production. I'll backup the files to a save file in the production job for a while and when time permits I'll set up a test job and cancel it immediately to see what happens.
Thank you all again,
GG
@Joachim:
DeleteQuery/400 is a "read-only" facility.
Query Management is not.
You can execute INSERT, UPDATE, and DELETE SQL statements (and DDL statements, for that matter - CREATE TABLE, etc.) that were compiled into a *QMQRY (CRTQMQRY / STRQMQRY). You can create QM procedures that start under various isolation levels, start connections to other systems, run multiple QM queries and nested QM procs, and issue COMMIT operations (STRQMPRC).
Interesting .... I dug deeper into my old code, Paul Conte's (albeit dated) book and was experimenting more this morning .... technically what I was doing under 4.5 was a normal batch or interactive OPM CL environment so apparently the use of the QM to receive parameters in that environment was limited to read only. Building the journal in the CL before the QM reference did allow updates and inserts. Regardless, the new 7.1 techniques and the commitment control specific parameter are a whole lot more concise. Best advice I have received in a very long time :)
DeleteGary, thanks for clearing that up. It's quite a while that I used QM and even then only occasionally. Pardon me - somehow I knew there could be the risk being off the track here ;-)
DeleteJoachim, you mentioned "OS actually does not implicitly manage commitment control anywhere". I believe it does - slightly - in the following circumstance: if you are currently journalling just "after" images [which I avoid by the way] instead of "both" before and after images; when any file journalling "after" images is opened under commitment control, the system automatically starts journalling "both" images as the "before" image is required to rollback, When commitment control is ended, the system reverts back to "before" images. This applies if running an SQL command under commit.
DeleteJust checked and an extract from manual on Start Commitment Control: Before a commitment definition is established, the user must ensure that all database files that are to be opened under commitment control for a single commitment transaction are journaled. If only the after images are being journaled, the system implicitly begins journaling both the before and the after images for the duration of the changes being made to files opened under this commitment definition.
QMQRY has long been able to do updates.
DeleteYou could also create a imbedded SQL program of about 13 lines that would run the same kind of statements as RUNSQL; the code takes 15 minutes to write and can be located for download. This gets around QMQRY's 50 byte variable limit.
All this is very easy to implement. What is more difficult is to get the data back into CL variables. Creating a table and reading it with DCLF is doing record level access from CL, not SQL. A Select into command or program that returns one row/one variable and a program that passes a cursor and reads back one variable for each row is not particularly difficult, but filling a structure is a lot more work.
Tony, what you mention is exactly what I meant by "implicitly". I am aware that the OS/DB takes care of the commitment handling once you as a devoloper/user decide to use it. I am not aware that the OS/DB will do commitment control if you decide to run everything under COMMIT(*NONE).
DeleteIBM's RUNSQL command was made available via PTF to 6.1 and 7.1. And as someone mentioned, this new command could cause confusion/problems if someone already has a RUNSQL command they created in the past. Here is a link to an article that discusses this issue:
ReplyDeletehttp://www.itjungle.com/fhg/fhg053012-story01.html
If you have some pre-existing code that has RUNSQL REQUEST(...) then it could be that it is a wrapper for QM. Many people created a RUNSQL command to simplify running SQL from CL and therefore have programs with RUNSQL REQUEST(...). But the new IBM RUNSQL command does not have REQUEST as a valid keyword, so these programs will suddenly fail (unless the programs qualify the command with the library where their custom RUNSQL command is located) once PTFs are applied that gives them the new IBM RUNSQL command (since IBM's command is in QSYS and will probably be found first by the program.)
So, if you already have programs with RUNSQL that pre-date IBM's RUNSQL command, the easiest fix might be to qualify that older RUNSQL command in each program where it is used.
Hi,
DeleteYou mentioned Qualify the older RUNSQL command means.Using libraryname followed by command in CL program.Is this fix the issue for the older runsql command?
LIB/RunSQL REQUES(....)
I have also used the RUNSQL Command, in a recent CL Program, and it's just an WOW. Before that I used to use QSH db2 utility to run dynamic SQL Command in CL. But having something as a command is Great...
ReplyDeleteWhen I make commands that I think IBM will very likely get around to writing in a few years, I generally try to name them something I don't think IBM will use. RUNSQL was just too likely. If I then had to go back and change all the programs that used it, I'd change the command to something quirkier instead. EXECDB2?
ReplyDeleteThis discussion was inspiring, and I wrote a select statement passing command that will process up to 8 concurrent cursors and fetch them into CL variables. Proved easier than I thought, if inelegant. However, while the V5R4 data structure support in CL helps a lot, it seems to give me only packed and integer numeric variables. It leaves me with defining zoned fields as character. Anyone have any insight? Or is that too far afield for this thread?
Last year I came back into the 'AS400' fold after several years absence...programming life is good again. I've been using the RUNSQL statement on V6R1 for quite some time now...Lynne, I would be curious to see your code on how to fetch SQL data back into CL variables...I've struggled with that in the past.
DeleteI agree about the naming commands... but I go one step further since the command EXECDB2 "could" have been a name used by another third party product. I name my command as I would any CL program using the naming convention of the application. For example one of our applications prefixes all objects with 'GP' so our implementation of RUNSQL would have been GPRUNSQL. It's just one more level to help keep us from stepping on any toes...
DeleteA variation of my previous performance suggestion:
ReplyDeleteInstead of using CHKOBJ, the RTVMBRD CL command could be used with the NBRCURRCD parameter. If the outfile currently exists, this returns the number of rows. If it doesn't exist, the CL can monitor for that, similar to CHKOBJ. The idea is to reuse the existing outfile only when it contains a small (by your definition) number of rows. Logic might be something like the below.
NOTE: This is an incomplete example to show concept, and has not been tested in any way.
DCL &CreateIt *LGL '0'
DCL &Rows *DEC LEN(10 0)
DCL &DropLmt *DEC LEN(10 0) VALUE(10000)
DCL &Select *CHAR LEN(100) +
VALUE(' SELECT FLD0,FLD1,FLD2 FROM TESTPF ...')
DCL &SqlStmt *CHAR LEN(200)
RTVMBRD ... NBRCURRCD(&Rows)
MONMSG MSGID(CPF9812 CPF9815) EXEC( CHGVAR &CreateIt '1' )
IF (&CreateIt *EQ '0') THEN(DO)
IF (&Rows > &DropLmt) THEN(DO)
/* Existing outfile has too many rows. Drop and recreate it. */
RUNSQL SQL('DROP TABLE QTEMP/TEMP_TABLE ') ...
CHGVAR &CreateIt '1'
ENDDO
ELSE DO
/* Reuse existing outfile and repopulate it. */
RUNSQL SQL('DELETE FROM QTEMP/TEMP_TABLE ') ...
CHGVAR &SqlStmt +
VALUE('INSERT INTO QTEMP/TEMP_TABLE ' *CAT &Select )
RUNSQL SQL(&SqlStmt) ...
ENDDO
ENDDO
IF (&CreateIt *EQ '1') THEN(DO)
CHGVAR &SqlStmt +
VALUE('CREATE TABLE QTEMP/TEMP_TABLE AS( ' *CAT &Select +
*BCAT ') WITH DATA' )
RUNSQL SQL(&SqlStmt) ...
ENDDO
Thanks for revealing that. It's quite useful.
ReplyDeleteHi.
ReplyDeleteI have a SQL command that runs in the STRSQL window, but not thru RUNSQL. I wonder why? The SQL statement just adds a column containing the current date.
Also, how can i save the output to a file? Thru STRSQL, i change the setting to output to file, then run the statement. But how can i do it using RUNSQL?
Thanks.
There are only certain SQL statements that you can run using the RUNSQL command. Here is a list of them here.
DeleteIf you are getting a message when you use the RUNSQL it would be a place to start to determine if RUNSQL is the problem.
Simon,
ReplyDeleteCan you take a minute and let me know if you can tell what might be wrong with this?
RUNSQL_X SQL('UPDATE mylib/myfile SET N53DSC = +
''' *CAT &TEXT *CAT ''' where ''' *CAT +
&N53GIDCHAR *CAT ''' = ''' *CAT +
&CHARID# *CAT ''')') COMMIT(*NONE)
When I run this inside my CLLE program, I get the following message:
Token ) was not valid. Valid tokens: OR USE SKIP WAIT WITH.
Thanks,
Barry
Make the SQL statement in a variable then use that in the RUNSQL statement.
DeleteThanks Simon. Good idea and not like I've never done that before. However I still get the same 'Toke ) was not valid' message.
DeleteThe statement as you have it would produce the following string:
DeleteUPDATE mylib/myfile SET N53DSC = '' WHERE '' = '')
I am using to indicate this is the value that is contained with &TEXT, etc.
1. The WHERE comparison field cannot be a variable. Should be: WHERE =
2. There is a ) at the end that does not match with an opening (. Remove it.
Is it possible to have the end user enter the values without having a screen? Like you can do with CL prompting?
ReplyDeleteIf you mean CL's Command prompting I do not see why not.
DeleteOne tip I would like to share. Sorry if someone suggested this already. Too lazy to read all of the posts. Anyhow - I use a variable to construct my SQL statement and then use the variable in the command - ie RUNSQL SQL(&SQLSTMT). By doing this I can then see the SQL if I am using ISDB by using "dsp (1:1200)sqlstmt". (assuming &SQLSTMT is 1,200 long. Makes it tons easier to see where I've messed up.
ReplyDeleteCan someone tell me (sorry if it's already been asked) is it possible to use a numeric variable in the CHGVAR build of the SQL statement? i use a CHAR field and I have no problem with my compile. The program ab-ends when it compares to a numeric field but it compiles. lol If I change the variable to DEC the CL will not compile. Is this possible? If so, what is the trick?
ReplyDeleteIt is not possible to use a numeric value when building a string that contains the SQL statement. It is only possible to concatenate alphanumeric fields together.
DeleteThe reason the program compiles is that it only knows that RUNSQL will use a variable for its statement, not what is in that string. Only when the program is executed does the RUNSQL get to "see" what is in the string.
Simon,
ReplyDeletewe have so many programs which using older version of RUNSQL with parameter REUEST.After IBM release new RUNSQL program causing many batch jobs to fail....
What are the ways i have to solve this issue..please let me know??
If you are using the non-IBM RUNSQL command & want to keep doing so then you are going to have to do some work.
ReplyDelete- You could put the library that contains the non-IBM RUNSQL at the top of the system library list. I would not do this as you will never be able to use the IBM RUNSQL, and could have problems with other commands.
- Change all of the programs and qualify the call to the command. The library containing the non-IBM RUNSQL would be added to the bottom of your user library list.
- Rename the non-IBM command to something different. Then change all of the programs to use renamed command name.
- Replace the non-IBM RUNSQL with the IBM RUNSQL. As you are going to have to change all the programs anyway this would be my preferred choice.
Hi,
ReplyDeleteI'm on 6.1 and I have RUNSQL in a CL program. The Statment is 'UPDATE ...'
and is ok but I need to get the SQL Msg SQL7957 with the number of rows
updated or the SQL0100 (row not found). I can get this with RCVMSG but only in debug mode. Can anyone help me?
Rui
Following the manual it should be done with RCVF but I don't know how, I'm also looking for a solution
ReplyDeleteStill a bad practice: The files used in RUNSQL does not show up in DSPPGMREF.
ReplyDeleteExample:
CL PROGRAM TESTSQL:
RUNSQL SQL('DROP TABLE QTEMP/MYTABLE') COMMIT(*NONE)
MONMSG SQL9010
RUNSQL SQL('CREATE TABLE QTEMP/MYTABLE AS ( +
(SELECT * FROM PRDTABLE)) WITH DATA') COMMIT(*NONE)
DSPPGREF:
Object
Program Referenced Library
TESTSQL QLECWI QSYS
TESTSQL QCLSRV QSYS
TESTSQL QLEAWI QSYS
RPG PROGRAM TESTSQL1:
EXEC SQL
Set Option Commit=*NONE,DatFmt=*ISO,CloSqlCsr=*ENDMOD,DlyPrp=*YES;
EXEC SQL
DROP TABLE QTEMP/TMPQRYF;
EXEC SQL
CREATE TABLE QTEMP/TMPQRYF AS (
SELECT *
FROM PRDTABLE A
) WITH DATA;
*INLR=*ON;
DSPPGMREF:
Object
Program Referenced
TESTSQL1 QSQROUTE
TESTSQL1 QRNXIE
TESTSQL1 QLEAWI
TESTSQL1 TMPQRYF
TESTSQL1 PRDTABLE
A quick question. Can we use 'SET' SQL command to set or change a variable's value similar to CHGVAR command?
ReplyDeleteFor example...
ChgVar &CurDate8 &Blanks (Initialize to blanks)
ChgVar &SETSQL ('SET &CurDate8 =' *Bcat '(SELECT current date FROM sysibm/sysdummy1)')
RUNSQL (&SETSQL)
This may not be the correct representation, but I hope you get the idea of what I'm trying to accomplish here.
Thanks in advance.
The SET is not supported in the RUNSQL command.
DeleteHere is a list of those SQL expressions that are recognized by this command here.
Clarification:
ReplyDelete*CAT (concatenation, symbol ||) operator
*BCAT (concatenation with blank insertion, symbol |>) operator
*TCAT (concatenation with trailing blank truncation, symbol |<) operator
http://www.ibm.com/support/knowledgecenter/ssw_i5_54/rbam6/rbam6charstrngexp.htm
Hi Simon,
ReplyDeleteYour second example, with passing parameters to a RUN SQL statement was very handy to me today.
Thanks very much for such detailed description of the examples.
I am glad you found this useful. It is good to get feedback, like this, from the readers of this blog.
Deletenumeric variables cant be used in RUNSQL. Lemme know if any change.
ReplyDeleteI myself figured out three patterns to write RUNSQL statements.
The only fact is RUNSQL needs statements like we do in STRSQL. - J
If you need to use numeric values in a RUNSQL you will need to convert them to character. You could use the %CHAR BiF.
DeleteYou would need to do the same in SQLRPGLE if you were building a string to be executed in SQL.
Good morning, lots of information here. I wanted to know if 'Select' is allowable yet on this command. Have seen other variations, like a RUNSQLLE command, but not sure how that would work with what I need. I am trying to find out how to get a Count(*) on a file with certain characteristics through SQL in a CL pgm. thanks for your time.
ReplyDeleteWhile Select is available in the RUNSQL command, it can only be used for certain types of things, creating tables is one example.
DeleteAlas, it is not possible to use Select in the RUNSQL statement to retrieve a value using INTO, such as a the count you desire. For that you will either have to output the count to a table, which you can then retrieve the value from, or use SQLRPGLE.
Hi Simon,
DeleteOnce i copy the count in another table , how can i fetch the same value into a variable
Use the RCVF command to read the row from the table.
DeleteSimon,
ReplyDeleteI am trying to create a schema using SQL in my CL program on the AS400, but I am getting a token error. I've included my code. Can you tell me why my code is wrong. I am trying to automate a few processes. Once I get over this hump, I should be able to proceed with automation. Thank you.
PGM PARM(&LIBNAME &SCHNAME)
DCL VAR(&LIBNAME) TYPE(*CHAR) LEN(10)
DCL VAR(&SCHNAME) TYPE(*CHAR) LEN(10)
DCL VAR(&SQLSTMT) TYPE(*CHAR) LEN(1000)
CHGVAR VAR(&SQLSTMT) VALUE('CREATE SCHEMA ''' || +
&SCHNAME || ''')')
RUNSQL SQL(&SQLSTMT) COMMIT(*NC) NAMING(*SQL)
ENDPGM
CRTSCHEMA SCHEMANAME(BONDELIB) CONVLIB(BONDEXXX)
CRTSCHEMA SCHEMANAME(BONDELIB) CONVLIB(BONDEXXX)
1000 - RUNSQL SQL('CREATE SCHEMA ''BONDEXXX '')') COMMIT(*NC)
NAMING(*SQL)
Token 'BONDEXXX ' was not valid. Valid tokens:
AUTHORIZATION.
RUNSQLSTM command failed.
SQL9010 received by procedure CRTSCH. (C D I R)
C
Application error. SQL9010 unmonitored by CRTSCH at statement 0000001000,
instruction X'0000'.
When you make the &SQLSTMT variable it should be:
DeleteCHGVAR VAR(&SQLSTMT)
VALUE('CREATE SCHEMA ' || &SCHNAME)
AWSOME! Thanks so much. It worked!
DeleteSimon,
ReplyDeleteIs it possible to use CL variables for the file names in the following SQL statement? This there also a way to generate a spool file of the results once the statement is run? I would like to include this SQL statement in my CL program. It is a check to make sure that all objects were copied from the original library into my schema. Thank you.
select cppgenlf.odobnm,cppgenlf2.odobnm
from cppgenlf
full outer join cppgenlf2
on cppgenlf.odobnm=cppgenlf2.odobnm
order by cppgenlf.odobnm
You can always make the statement in a variable and then execute that.
DeleteOutput the results to a table in QTEMP and then use a simple RUNQRY command will do it.
Broken link:
ReplyDeleteYou can learn more about the RUNSQL command on IBM's website here.
http://www.ibm.com/support/knowledgecenter/api/content/ssw_ibm_i_72/cl/runsql.htm?locale=en
IBM keep changing the URLs, and never put a redirect on the old URL to the new one.
DeleteI'm having am impossible time trying to incorporate a CL Variable into a RUNSQL statment. I've followed your examples above but they do not work. Any suggestions as this seems completely inpossible. I hate SQL
ReplyDeleteTrust me it does work for most SQL statements. But without examples I cannot help. Email me at simon@rpgpgm.com & when I get time I will see what I can do.
Delete