odb2 v 1.3  9-06-2000 

Author: John Goodyear
        johngood@us.ibm.com

odb2 is a MVS Unix Services adaptation of the adhoc SQL sample that's
distributed in the DB2 Universal Database samples directory.  It uses
dynamic SQL and the Call Attach Facility (CAF) to allow the execution
SQL commands from the USS shell against an MVS DB2 database.

This method seems to be faster than using the CLI interface supported by
DB2.  It is also simpler to set up since CLI requires additional
configuration to DB2 that's not done during the initial DB2 installation.

To skip to the section that describes how to build odb2, search for
the word: instructions


odb2 version 1.3
Usage: odb2 <-?> <-c cmt. char> <-f input file> <-i> <-l> <-q> <-t term char>
       <-v> <-F field delimiter> <-R record delimiter> <SQL command.>

 -?             Prints odb2 usage information.
 -c <cmt. char> Character used to mark input line as a comment. '-' is default
 -f <in file>   Names a file to use for input.  If the option isn't specified,
                stdin is used.
 -i             Toggles input behavior to opposite of what was compiled in as
                the default (line continuation '\' vs statement 
                termination ';').
 -m <max data>  Limits the max number of bytes to retrieve for a given
                column.  Helpful for selecting LOB data from tables. The 
                default is 1048576 bytes.
 -q             Quiet mode. Doesn't print column names or summary info.
 -t <term char> Character used to terminate statements with. ';' is the 
                default.  If the option is specified, but no value is 
                supplied, ';' will still be used. (for aix db2 compatibility)
 -v             Verbose mode. Echoes SQL commands before they are executed.
 -F <fd char>   Field delimiter char.  ' ' is the default.  If the option is 
                specified, but no value is supplied, there'll be no delimiter
                used between fields, nor will the columns be padded.
 -R <rd char>   Record delimiter char.  '\n' is the default.  If the option is
                specified, but no value is supplied, there'll be no delimiter
                used between records
 "SQL command"  A quoted SQL command to issue to the Database.  If none
                is issued, odb2 goes into a primitive command mode

 NOTE: Be careful in using options F,R, or t without a value just before the
                sql string.  If done, the 1st char in the sql string will 
                become the value for the option.  Use -F '' before your sql 
                string instead.


odb2 issues one COMMIT per invocation, so if you go into command mode, the 
commit won't be issued until the command "quit" is issued.  


Using the -F and -R options with empty arguments is handy when you want 
to extract binary data from a column within a record and redirect output
to a file, but don't want the extra space and newline.

If the environment variable DB2SYS is set, odb2 will try to connect to that
subsystem.  Otherwise, it uses the name of the DB2 subsystem as defined in 
makefile.conf (DB2INST).  See the section under BUILDING FOR MULTIPLE 
SUBSYSTEMS for installing a package and plan under more than one subsystem.

LOB DATA:
Please note that LOB data, and any other fields with large data lengths may
be truncated when retrieved.  To try to control the amount of data output, a
value of 4K was chosen as the maximum column output size.  This number is 
somewhat arbitrary and can be either overridden by the -m command line 
option, or by compiling in a new default (see the MAX_COLUMN_OUTPUT macro
in the makefile).




BUILDING ODB2

  Several tools are used to build odb2:

    cwrap:  A utility to wrap c and c++ source code at 72 chars/line to 
        keep the DB2 precompiler happy.

    db2pb: By David Keegan.  This package is available from the OS/390
        Tools and toy page.  It has been enhanced to report precompiler
        errors and use cwrap to prepare the embedded sql code.  Requires
        that your environment previously defines STEPLIB (see below)

    DSNHDE: As of odb2 v1.1, no longer needed, but included for 
        other potential development needs.

        DSNHDE is an enhanced version of DB2's DSNH precompiler clist.
        It has been modified to pass the C(FOLD) option to the precompiler
        so that the embedded SQL code doesn't have to refer to database
        objects and keywords in uppercase.

    install: a simple script to bind the adhoc package to a db2 instance
        and create the odb2 plan.  Meant for situations where you want
        to move the odb2 executable and package to different systems
        and install them there without having to recompile all of the 
        source.  See install for more info.

    db2headers: For some reason,the sqlda header file isn't shipped in
        the SDSNC.H PDS for DB2V6.1.  I guess development thinks that
        if one wants to reference a sqlda structure one should 
        preprocess that code and include the EXEC SQL INCLUDE SQLDA 
        macro.  

        I disagree, this requirement runs against the grain of most of
        the other platforms.  It makes porting applications to OS/390
        harder.  So, the sqlda header is in this directory.  


  Any references to the directory odb2 should be interpreted as odb2vX.X 
  where X.X is the version of the odb2 package downloaded 


instructions

  To build odb2, perform the following steps:

    -environment settings:
    Put these in your .profile, and/or make sure they get propagated to
    your current environment.

      export _CXX_CCMODE=1
      export _CC_CCMODE=1
      export _C89_CCMODE=1
      export STEPLIB=DSN610.SDSNLOAD:DSN610.SDSNEXIT

  The 1st 3 lines are for getting around the annoying POSIX 
  compliance that the MVS make utility enforces regarding
  operands and options. 
 
  ** You'll have to modify the STEPLIB statement to reflect the 
  high level qualifier that your DB2 libs are in.  

 - Uncompress the archive
   The odb2 archive is compressed with the standard unix compress
   utility.  To uncompress it, issue:

   uncompress odb2vx.x.tar.Z

 - Untar the archive.
  odb2 is an ASCII tar.  To untar it on 390, Issue:

  pax -o "from=ISO8859-1,to=IBM-1047" -rf odb2.tar

  This will create the directory odb2 with the subdirectories 
  cwrapd and dbcon.  

 - Setup makefile.conf

  Edit makefile.conf, update the DB2HLQ macro to reflect the 
  hlq for your db2 installation.

  Update the DB2INST macro to reflect your DB2's subsystem id.
 

 - copy DSNHDE to your db2's SDSNCLST PDS.  
 
  NOT NEEDED ANYMORE.  (as of odb2 v1.1)
  If you're just building odb2, you don't need this.  If you
  want to use DSNHDE for some embedded SQL code that isn't all 
  upper case, read the NOTE below.

  NOTE: DSNH seems to  hard code the high level 
  qualifier that DB2 was installed under.  The default for our 
  version (DSNHDE) is DSN510.  If your system's DB2 is 
  installed under a different HLQ., you'll have to change
  this, or pass a different value for PCLOAD to DSNHDE.



 - cd to cwrapd. issue make.  
  After it's built, copy the cwrap executable to a directory in
  your PATH environment variable.  

 - Copy db2pb to a directory in your path as well. 

 - If you're an  unprivileged user, you will have to get your dbadmin
  to issue the follow database commands to allow you to build 
  odb2:

  grant bindadd to <username>
  grant create in collection odb2 to <username>

 - In the odb2 directory, issue make.  The will precompile, compile 
  and link the odb2 executable.  To do this you'll need 
  bindadd and create-in privileges in the DB2 instance that 
  you're working with.
  
 - Also, for the first time you build the application, issue:
  "make plan".
  This will create the DB2 plan ODB2 that uses the package 
  ODB2.ADHOC that was bound to the database during the 
  earlier build phase.

  Also, to allow others to use odb2, execute this db command: 

  grant execute on plan odb2 to public


 At this point odb2 should be ready to run.

BUILDING FOR MULTIPLE SUBSYSTEMS.

    If you want to setup odb2 be able to switch between multiple subsystems,
you'll need to modify the $(PROGNAME) and plan targets of the makefile as 
done below.  In this example, the odb2 package is created in subsystem DSN2 
as well as the default DSNC. Also, when the "plan" target is built, the
plan is created in subsystem DSN2 as well as DSNC.

$(PROGNAME): adhoc.o util.o;
        $(LINK) $(LINKFLAGS) -o $(PROGNAME) adhoc.o util.o $(LIBS)
#   add any binds to other db2 subsystems here
        export DB2HLQ=$(DB2HLQ); db2pb adhoc.dbrm "SYSTEM(DSN2)"

plan:
        export DB2HLQ=$(DB2HLQ); db2pb "SYSTEM($(DB2INST)) BIND(YES) \
               PLAN(ODB2) PKLIST('ODB2.ADHOC')"

#   Add any other db2 subsystems that need the plan
        export DB2HLQ=$(DB2HLQ); db2pb "SYSTEM(DSN2) BIND(YES) PLAN(ODB2)\
               PKLIST('ODB2.ADHOC')"


COMMON PROBLEMS

1) Error seen:

 Compiler error - Incorrect suffix:

 Solution:

 See the BUILDING ODB2 section above.

2) Error seen:
   Authorized commands are not supported.

     DELETE 'DB2ADM.adhoc.sqc'
     FOMF0139I Authorized commands are not supported

   Solution:

    The default OS/390 authorized commands list (found in 
    SYS1.PARMLIB(IKJTSOxx) has DELETE in the list. Anything in this list 
    can't be issued via the TSO command.

    Have your system programmer remove DELETE from the list.  If (for what 
    ever reason) he/she doesn't want to, you can get around this problem by 
    modifying db2pb to pass -o as an option to the tso command. *BUT* This
    only works if you're in a 3270 omvs session.

3) Error Seen:

        IKJ56228I DATA SET DSN610.SDSNLOAD NOT IN CATALOG OR CATALOG CAN NOT
        BE ACCESSED
        IKJ56701I MISSING DATA SET NAME+
        IKJ56701I MISSING DSNAME (MEMBER NAME)
        FSUM8226 make: Error code 8

   Solution:
 
    Check the DSNH CLIST in (usually ) in <DB2HLQ).SDSNCLST.  The PCLOAD   
    value in DSNH probably needs to be updated to reflect the location of
    SDSNLOAD on your system.

4) Error Seen:
        cc -I"//'DR5A.REF.SDSNCHDR'" -c dbcon.c
        FSUM3066 The COMPILE step ended with return code -1: EDC5083I An 
        error occurred attempting to load a module into storage.

            FSUM3017 Could not compile dbcon.c. Correct the errors and try 
            again.
            FSUM8226 make: Error code 3 

   Solution:

    Add SYS1.SCBCCMP to your STEPLIB where SYS1 is the HLQ that the compiler
    was installed under.


If you have any other problems, send me email with the output of your 
make.




CHANGES:
Version 1.3
 JCG

                Added the macro MAX_COLUMN_OUTPUT to the makefile.  This can
        be used to set the default maximum column output.  This behavior is
        also governed by the -m option.

                Now supports the retrieval of LOB data.  Be careful displaying
        binary data directly to the terminal.  It can lobotomize your 
        terminal session.
 
                Updated command line parsing to allow options to be specified 
        together, (i.e. odb2 -vf input.sql )
 
                Renamed the cwrap directory to cwrapd.  This allows the cwrap 
        executable to be copied up to the odb2 directory after it's built.
  
                Updated cwrap.  Now at version v.2.2.  See the readme in the
        cwrapd directory.
  
                Updated db2pb to do a bit more error checking.

                Taught the makefile to automatically make libdbcon.a if it 
        doesn't already exist.

                Added the -m option for limiting the size of columns.  This 
        is useful for retrieving LOB data or just arbitrarily shortening 
        column widths.

                Setup option parsing logic to allow specifying -t without an 
        argument.  This supports behavior similar to the aix db2 clp.   
  
                Changed the -d option to -F
  
                Added the -R option.  Both -R and -F can be specified without
        any values so that no field or record separators will be used.

                Removed the -l option.  Was never implemented.

                Reworked statement reading logic to look for a statement 
        termination character: ';' by default. An option is available to 
        define which char to use. (see -t)
  
                Changed default comment char to '-'.  Also added an option to 
        define which character to use. 

                Changed verbose printing to send output to stdout instead
        of stderr so that the print SQL statements are in sync with the query 
        output.  Did the same for error reporting. (mixed blessing?)

                Added a print statement to indicate what the result of each 
        SQL statement executed is.  For situations where the return code is
        0, by default, the string "DB20000I" is prepended to the message. If,
        for some reason this is undesireable, remove the AIX_SUCCESS define
        from the makefile.

                Added support for the echo keyword.  If the first word in a
        SQL statement is the word "echo", the rest of the statement is just 
        printed to stdout, and the next line of input is processed.  

                Updated command mode logic to change the prompt when odb2 is 
        waiting for the user to terminate an SQL command.
  

Version 1.2
        Oops.

Version 1.1

10-23-1999
        JCG

                Removed any lower case SQL statements so that the DB2 supplied
        DSNH can be used to build odb2.

                Added comment support.  Now that odb2 reads from scripts
        better, added the ability to recognize the '#' character as a 
        comment.  If the 1st non whitespace character in a line is '#',
        odb2 will skip that line.

                Re-worked the dbcon CAF code to print better diagnostics
        of any problems encountered while connecting/disconnecting from
        DB2.  The return code and reason code are printed so that they
        can be cross-referenced with the DB2 Messages and Codes manual. 

10-08-1999
        JCG
                Modified db2pb and cwrap to be able to write directly
        to an MVS dataset.
                
                Added some logic so odb2 will behave better when reading
        from redirected input.  It now removes a trailing semicolon
        if it exists.  Blank lines are ignored.  

                Added option -v to control echoing of the sql command as
        it's processed.

                (Hopefully) fixed any potential buffer overwrite issues
        when reading inputted data into the DB2 statement buffer.

9-27-1999
        JCG
                Added command line option to suppress the printing of column
        names and summary info ( -q )
  
        Added command line option to specify the record delimiting 
        character. ( -d<char_val> ) 

Version 1.0.1

7-7-1999

        JCG  
                Added logic to call free() on the sqlda pointer passed into 
        init_da() (if not NULL).     

                In alloc_host_vars(), for all data types being displayed
        as type SQL_TYP_NCSTR, added one byte to the sqllen field, and  
        to the size of the buffer allocated to hold the data.  This was
        a problem in displaying at least columns of type DATE.

                Updated the readme to include information for building and 
        using odb2 against multiple DB2 subsystems.

7-26-1999
        JCG
                Removed more memory leaks in process_statement() where
        free_da() wasn't being called in all return paths

7-28-1999
        JCG
               Changed prompt from "dbii>" to "odb2 >"