CL programmers sometimes need to create physical files (PFs) on the fly. To create a PF that has an external definition, you have to use either DDS or DDL, and for CL programmers, that means having a separate source member that contains the file definition. Or does it have to mean that?

This article demonstrates how you can take advantage of QShell from a CL program to create a PF in which you can embed the source code, including the field definitions, inside the code of the CL program itself.

The trick to producing a PF on the fly from CL is QShell's db2 utility. This utility runs an SQL statement that's passed as a parameter. Because QShell commands can be run from CL's STRQSH command, embedding an SQL statement in a CL program is relatively easy. For example:

PGM

   STRQSH CMD('db2 "create table SOMELIB.SOMEFILE ( +
                       field1 decimal(5,0), +     
                       field2 char(30), +         
                       field3 date +              
                    )"')                           

ENDPGM

The preceding CL program creates a PF named SOMEFILE, located in the SOMELIB library. It defines fields named field1, field2, and field3.

As with any QShell command, you can prevent this command's output from being printed on the screen by setting the QIBM_QSH_CMD_OUTPUT environment variable to NONE. If you don't set the variable this way, the QShell command may print messages on the screen that would be displayed to your user.

Similarly, you can tell QShell to interrupt your CL program with an *ESCAPE message when a QShell command fails. To do so, set the QIBM_QSH_CMD_ESCAPE_MSG environment variable to Y. This way, you can use CL's MONMSG command to capture errors.

Another useful technique is to define some environment variables of your own. Anytime you want an environment variable's contents inserted into a QShell statement, all you have to do is include the environment variable preceded by a dollar sign. Consider the following code:

PGM

    DCL VAR(&MYLIB)  TYPE(*CHAR) LEN(10)   VALUE('SOMELIB')
    DCL VAR(&MYFILE) TYPE(*CHAR) LEN(10)   VALUE('TESTFILE')
    DCL VAR(&CMD)    TYPE(*CHAR) LEN(1000)

    CHKOBJ OBJ(&MYLIB/&MYFILE) OBJTYPE(*FILE)
    MONMSG MSGID(CPF9801) EXEC(DO)

        ADDENVVAR ENVVAR(QIBM_QSH_CMD_OUTPUT) VALUE(NONE) +
                  REPLACE(*YES)
        ADDENVVAR ENVVAR(QIBM_QSH_CMD_ESCAPE_MSG) VALUE(Y) +
                  REPLACE(*YES)
        ADDENVVAR ENVVAR(MYLIB)  VALUE(&MYLIB)  REPLACE(*YES)
        ADDENVVAR ENVVAR(MYFILE) VALUE(&MYFILE) REPLACE(*YES)

        CHGVAR VAR(&CMD) +
        VALUE('db2 "create table $MYLIB.$MYFILE ( +
                        field1 numeric(5,0), +
                        field2 char(30), +
                        field3 date +
                    )"')

        STRQSH CMD(&CMD)
        MONMSG MSGID(QSH0000) EXEC(DO)
           SNDPGMMSG MSGID(CPF9897) MSGF(QCPFMSG) MSGTYPE(*ESCAPE) +
                     MSGDTA('Unable to create file to copy to')
           RETURN
        ENDDO

    ENDDO

ENDPGM

In the preceding code, I use the CHKOBJ CL command to see whether I have a file named TESTFILE in the SOMELIB library. The file name and library names are variables that you could calculate at runtime. Heck, your program could even receive these as parameters if it wanted to!

If the file doesn't exist, I want to create it. So I set up QShell to prevent messages from going to the screen and to send errors to my program. Then I also set my own environment variables that contain the library and file name.

Note that my QShell command (the db2 command that runs the Create Table statement) now has $MYLIB.$MYFILE for the name of the file to be created. Because of the dollar sign characters, the QShell command interpreter looks up the values of the MYLIB and MYFILE environment variables and inserts them into the command. It does this before the db2 utility runs.

I set the MYLIB environment variable to SOMELIB by using the ADDENVVAR CL command, and I set the MYFILE environment variable to TESTFILE. That way, when my QShell command runs, it creates a table called SOMELIB/TESTFILE.

You can use environment variables anywhere in the statement. For example, to start every field in your file with a two-character prefix and have this prefix be variable, you could do something like this:

PGM

    DCL VAR(&MYLIB)  TYPE(*CHAR) LEN(10)   VALUE('QGPL')
    DCL VAR(&MYFILE) TYPE(*CHAR) LEN(10)   VALUE('CUSTMAS')
    DCL VAR(&CMD)    TYPE(*CHAR) LEN(1000)

    CHKOBJ OBJ(&MYLIB/&MYFILE) OBJTYPE(*FILE)
    MONMSG MSGID(CPF9801) EXEC(DO)

        ADDENVVAR ENVVAR(QIBM_QSH_CMD_OUTPUT) VALUE(NONE) +
                  REPLACE(*YES)
        ADDENVVAR ENVVAR(QIBM_QSH_CMD_ESCAPE_MSG) VALUE(Y) +
                  REPLACE(*YES)
        ADDENVVAR ENVVAR(MYLIB)  VALUE(&MYLIB)  REPLACE(*YES)
        ADDENVVAR ENVVAR(MYFILE) VALUE(&MYFILE) REPLACE(*YES)

        ADDENVVAR ENVVAR(MYPREFIX) VALUE('CS')

        CHGVAR VAR(&CMD) +
        VALUE('db2 "create table $MYLIB.$MYFILE ( +
                        ${MYPREFIX}cust numeric(5,0), +
                        ${MYPREFIX}name char(30), +
                        ${MYPREFIX}addr char(30), +
                        ${MYPREFIX}city char(15), +
                        ${MYPREFIX}stat char(2), +
                        ${MYPREFIX}zip  char(10) +
                    )"')

        STRQSH CMD(&CMD)
        MONMSG MSGID(QSH0000) EXEC(DO)
           SNDPGMMSG MSGID(CPF9897) MSGF(QCPFMSG) MSGTYPE(*ESCAPE) +
                     MSGDTA('Unable to create file to copy to')
           RETURN
        ENDDO

    ENDDO

ENDPGM

In the preceding example, a file named CUSTMAS is created in the QGPL library. The file contains fields named CSCUST, CSNAME, CSADDR, CSCITY, CSSTAT, and CSZIP. If you want these fields to all begin with ZZ instead of CS, all you need to do is change the MYPREFIX environment variable to ZZ instead of CS.

You could put your SQL statement into a separate source member and run it with RUNSQLSTM, STRQMQRY, and so on. But sometimes you want to code the SQL statement inline as part of your program's code. That ability, along with the elegance of using environment variables as substitution parameters, makes STRQSH a valuable tool in the CL programmer's toolbox.