DB2/OS390 Unix Precompiler/Binder db2pb.sh. David Keegan 980622. Introduction ============= Db2pb.sh invokes the DB2/OS390 Precompile/Bind utility DSNH (an MVS CLIST) from the OS390 Unix Shell, and provides it with a limited interface to HFS files. You can invoke it in the following ways: 1. To precompile embedded SQL residing in a HFS file, bind the resulting Database Request Module (DBRM) to a DB2 Package, and produce HFS copies of the precompiler output files (C/C++ source and DBRM): db2pb.sh Epn Spn Dpn ["DsnhUserParameters"] 2. To bind a DB2 Package from a HFS DBRM file: db2pb.sh Dpn ["DsnhUserParameters"] 3. To perform an arbitrary DSNH operation (no HFS files processed): db2pb.sh "DsnhUserParameters" 4. To display help text: db2pb.sh [-h] Arguments: Epn=EsqlPathName (HFS embedded SQL precompiler input). Spn=SourcePathName (HFS C/C++ source precompiler output). Dpn=DbrmPathName (Database Request Module input or output). DsnhUserParameters=Parameters for the DSNH utility. All DSNH parameters can always overridden on the db2bp.sh command line, so you can run DSHN in arbitrary ways. All of its MVS capabilities are available. HFS files are only processed if they are specified as arguments to db2pb.sh (cases 1 and 2 above). Prerequisites ============== OS390 Version 2 Release 1. DB2/OS390 Version 5. Arguments ========== EsqlPathName ------------- The HFS Path Name of the precompiler input source file (with embedded SQL). The suffix is not significant. If you specify this a precompile and package bind are done by default, and you must specify SourcePathName and DbrmPathName. The Package Name is derived from this argument by stripping off the directory and suffix. An embedded SQL file with any line longer than 72 characters is rejected with an error message, as the offending line would be silently truncated by the DB2 precompiler, probably resulting in erroneous results. You must ensure your embedded SQL lines are no longer than 72 characters. SourcePathName --------------- The HFS Path Name for precompiler C/C++ output. If this has a ".c" suffix, the precompiler source language is set to C. Otherwise it defaults to C++. Specify this only if the EsqlPathName is specified, and specify it as the second argument to db2pb.sh. DbrmPathName ------------- The HFS Path Name of the DBRM. If this argument is specified it MUST have the suffix ".dbrm" and must be either the first or the third argument to db2pb.sh. If it is the first argument it is an output file. Otherwise it is an input file, and is bound to a package by default, in which case the pacakge name is derived by stripping off the directory and suffix. DsnhUserParameters ------------------- This argument specifies additional parameter(s) for the DSNH utility. It is passed to DSNH without modification or interpretation, and placed after parameters calculated by db2pb.sh itself, so it takes precedence. This gives you full control over the operation of DSNH. Use double quotes to prevent the Unix shell interpreting parentheses, inappropriately. DSNH and its parameters are documented in "DB2 for OS390 V5 Command Reference". Db2pb.sh evaluates several parameters for DSNH automatically, based on its own file arguments. For example: INPUT(''''''): The fully qualified name of the MVS Input Dataset. It is derived from the name of the input HFS file (EsqlPathName or DbrmPathName) by removing the directory part and prefixing with $LOGNAME as a High Level Qualifier. If there is no input HFS file, a dummy name is used, as INPUT is a mandatory argument, and parameters BLIB(NONE) BDMEM(NONE) are passed to prevent the dummy name being converted into a non-existant library name. OUTNAME(db2pb): This is used as a component of MVS output dataset names. HOST(CPP) or HOST(C): This specifies the source language. It is based on the suffix of SourcePathName (see above). BIND(NO), COMPILE(NO), PRELINK(NO), LINK(NO), RUN(NO): These DSNH operations are suppressed by default. PRECOMP, PBIND: These parameters control the precompile and package bind operations respectively. Each is set to NO to suppress the operation unless db2pb.sh considers the operation is appropriate based on its file arguments, in which case it is set to YES. You will most likely need to override the default Subsystem Name and probably the Run Time Implicit Table Qualifier in DsnhUserParameters: db2pb.sh ... "SYSTEM(SubsystemName) PQUALIFIER(ImplicitTableQualifier)" If you want to bind existing packages to a plan, you must enable the bind plan step specify the plan name and the package list in DsnhUserParameters, for example: db2pb.sh "BIND(YES) PLAN(PlanName) PKLIST('PackageName1,...) Plans and Packages =================== The DB2 documentation is a bit vague on how an application uses plans and packages, especially as regards Unix programs. It seems to work something like this: Each DB2 application needs a "plan" in order to execute SQL statements. The plan is established when the first SQL statement (probably SQL CONNECT) is executed. By default the plan name is derived from the name of the embedded SQL Dataset which contains the CONNECT statement by removing prefixes and suffixes. Thus the plan name is not necessarily identical to the program name. If your program has embedded SQL and also uses the DB Call Level Interface (CLI), you can connect to the data source via CLI, which will establish a plan name of DSNACLI. Your embedded SQL statements can implicitly use the CLI connection, and you don't need an additional embedded CONNECT statement. When a source file containing embedded SQL is precompiled, a Database Request Module (DBRM) is produced. This is an MVS Dataset (usually with the suffix .DBRM) containing the SQL statements and associated information. The DBRM must be bound to the application plan, either directly, or preferably via a "package". Typically you should bind a package from each source file in your program which contains embedded SQL, and then bind all the packages into an application plan using the DSNH PKLIST parameter. With this approach there is no need to rebind the plan when a source file changes, just precompile the source file and rebind the associated package. See the example below. Package names have the form Location.CollectionId.PackageName, where Location is normally specified as blank for the current location. The default for PackageName is derived from the Input filename by removing the directory and suffix parts, and the default CollectionId is identical to PackageName. To identify a package when binding a plan, you must specify the CollectionId as well as the PackageName, that is "CollectionId.PackageName". Environment Settings ===================== TMPDIR ------- Temporary HFS files are created in this directory. It is optional and defaults to /tmp. LOGNAME -------- This is used as the High Level Qualifier for the names of datasets created in MVS. Mandatory. DB2HLQ ------- This is the High Level Qualifier of the DB2 files and libraries installed in MVS, in particular the library containing the DSNH CLIST. It is optional, and defaults to DSN510. Example ======== A program is built from two C/C++ embedded SQL source files, main.sqc, which implements the program entry point main(), and has an embedded CONNECT statement, and lib.sqc, which implements functions called by main(). 1. To precompile lib.sqc and bind the resulting DBRM to package LIB.LIB: db2pb.sh lib.sqc lib.C lib.dbrm "SYSTEM(DBA1) PQUALIFIER(BUILD)" The precompiler source output is written to lib.C and the DBRM output is written to lib.dbrm. The final argument overrides the DSNH defaults for the Bind Package step, specifying the DB2 Subsystem Name and Runtime Table Qualifier. 2. To precompile main.sqc, bind the resulting DBRM to pacakage MAIN.MAIN, and bind application plan MAIN: db2pb.sh main.sqc main.C main.dbrm\ "SYSTEM(DBA1) PQUALIFIER(BUILD)\ BIND(YES) PLAN(MAIN) PKLIST('MAIN.MAIN,LIB.LIB')" This is similar to step 2 above, but additional parameters tell DSNH to bind a plan named MAIN and add packages MAIN.MAIN and LIB.LIB to its package list. Note the single quotes around the value specified for PKLIST. This is required by the MVS TSO parameter syntax. Also each package in the list is specified as "CollectionId.PackageName" with the CollectionId explicit. 3. Compile/link program main in the usual way from main.C and lib.C produced by steps 1 and 2 above. 4. To install program main on another system, bind each DBRM file produced in steps 1 and 2 above to a package on the new system, and bind the packages to a plan: db2pb.sh lib.dbrm "SYSTEM(DBA2) PQUALIFIER(TEST)" db2pb.sh main.dbrm "SYSTEM(DBA2) PQUALIFIER(TEST)\ BIND(YES) PLAN(MAIN) PKLIST('MAIN.MAIN,LIB.LIB')" Problems ========= Allocation ----------- You may get a dataset allocation error when running db2pb.sh, and messages: RC(12) IKJ56220I DATA SET DAVEK.TEMP.DBRM NOT ALLOCATED, TOO MANY DATA SETS+ IKJ56220I MAXIMUM NUMBER OF DATA SET ALLOCATIONS ALLOWED BY YOUR SESSION HAS BEEN REACHED, YOU SHOULD FREE UNUSED DATA SETS This is probably due to a missing DYNAMNBR setting in your OpenEdition APPC Transaction Profile. You can adjust this as follows: 1. Run the APPC Administration Dialogue. Type "tso ICQASRM0" at an ispf command prompt. 2. Select "TP Profile Administration Current TP Profile" with an "s" prefix. You get a panel with a "TP Profile Name" list at the bottom. 3. The profile with the strange name containing X'30'078 is for Open Edition. Edit this with an "e" prefix. 4. Type Enter to see successive pages of data until you see "Edit copied JCL" at the bottom of a panel. Enter YES here. 5. Edit the JCL so the DYNAMNBR parameter of the EXEC PGM step is at least 100. It should look something like this: //STEP1 EXEC PGM=BPXPRFC,DYNAMNBR=100 6. Enter SAVE and the END at the EDITOR Command Prompt. You should see "Press ENTER to finalize the EDIT operation. ICQAS591". Press ENTER and you should see "TP Profile has been updated. ICQAS530". Now db2pb.sh should run without allocation problems.