Search code examples
ibm-midrangedb2-400rpgle

CRTSQLCI v RUNSQLSTM

I am running this on OS 7.4.

CREATE PROCEDURE MYLIB.MYSQLPROCEDURE1(PARM1 IN INT)    
LANGUAGE SQL                                            
SPECIFIC MYQ001                                        
BEGIN                                                    
END;

If I run that using 'RUNSQLSTM' the code is compiled and run and a Program MYQ001 is created with spool files of CRTSQLCI and CRTCMOD with no errors.

As soon as i do it manually using CRTSQLCI with *NOGEN , this gives me a code :-

CRTSQLCI OBJ(MYLIB/MYQ001) SRCFILE(MYLIB/QSQLSRC) SRCMBR(MYQ001) COMMIT(*NONE) OPTION(*NOGEN) CLOSQLCSR(*ENDMOD) TOSRCFILE(QTEMP/QCSRC)

When I try and feed the code generated in QTEMP/QCSRC by the above to create the module:-

 CRTSQLCI OBJ(MYLIB/MYQ001) SRCFILE(QTEMP/QCSRC) SRCMBR(MYQ001) COMMIT(*NONE) OBJTYPE        
 (*MODULE)  OUTPUT(*PRINT)
 CLOSQLCSR(*ENDMOD) TOSRCFILE(QTEMP/QMODSRC)

I get a "SQL precompile failed."

SQL0011  30      58  Position 1 Comment not closed.                         
SQL5008  30      57  Position 11 Right parenthesis or right brace not found.
SQL0011  30      58  Position 1 Comment not closed.                         
SQL0053  10          No SQL statements found.

Why does it all work using RUNSQLSTM but not CRTSQLCI?

Would appreciate any help to resolve, even if its a case to say , its not possible this way?

Regards, JemRug

Solution

  • I think I understand your confusion. It seems you think that RUNSQLSTM is calling CRTSQLCI and CRTCMOD, and maybe even CRTSRVPGM, and that CRTSQLCI is simply running against the SQL source that you typed into RUNSQLSTM. Every bit of this is wrong.

    What these programs do:

    RUNSQLSTM - SQL client passes SQL statements to the DB2 for i database for interpretation and execution.

    CRTSQLCI - C precompiler that turns embedded SQL code into native C code. This calls one of the C compilers to produce a module or a program object.

    CRTCMOD - C compiler that produces a module. This one has no SQL Precompiler.

    CRTSRVPGM - Linker that ingests modules to build a service program.

    Note that while CRTSQLCI turns embedded SQL into native C code, the SQL you type into RUNSQLSTM is not formatted correctly for CRTSQLCI to turn it into native C code. There is an entire other manual that tells you how to write C code with embedded SQL. That is what CRTSQLCI needs. It does not look at all like standalone SQL statements. Coding SQL Statements in C and C++ Applications

    So what is happening? Why is it that every SQL client that can connect to DB2 for i can create and compile DB2 for i routines? Do they all have to convert the stored procedure code to C with embedded SQL and pass that to CRTSQLCI? No, none of them do. SQL clients simply pass the raw SQL to DB2 for i. The server itself knows how to generate the C code with embedded SQL that is passed to the precompiler. The precompiler then turns the embedded sql statements into native C code and then passes the resulting source to the C compiler, and finally DB2 passes the resulting module to CRTSRVPGM to turn it into a service program. The final service program name is stored in the SQL catalog so future calls to the routine can be executed directly.

    So it appears that you are missing a few steps. But if you know C, you can write a shell in C that allows you to insert virtually any SQL stored procedure you want and then compile it into a program using CRTSQLCI, then execute the resulting program object to create the service program.

    Or if you know RPG, you can use that to do the same thing. But, instead of using CRTSQLCI you would use CRTSQLRPGI to create the program object.

    Or, you could just use RUNSQLSTM because that will do all of this in a single step.