Results 1 to 7 of 7
  1. #1
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322

    Unanswered: DB2 OS/390 Dynamic SQL in COBOL

    Anyone have an example or a link to some code, or directions on how to do it?

    The manual is as clear as mud....
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Wow...almost 3 years...

    Still looking for examples of Dynamic SQL in COBOL for OS/390 (IBM Mainframe)

    Anyone got any or sites to look at?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    ---
    "It does not work" is not a valid problem statement.

  4. #4
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    The complexity depends on which of the following you need. The first two are relatively simple:

    • Non-SELECT statements.
    • Fixed-List SELECT statements. In this case, you know the number of columns returned and their data types when you write the program.
    • Varying-List SELECT statements. In this case, you do not know the number of columns returned and their data types when you write the program.


    Check out this manual. There is some sample code (pg 345).
    http://publib.boulder.ibm.com/epubs/pdf/dsnapk14.pdf
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  5. #5
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    I am trying to generate SQL based on a CONFIG File that defines the data in a flat file

    Basically like the following (written in SQL Server)

    Code:
    CREATE TABLE #Data_File (Col1 varchar(8000))
    GO
    
    /*
    The Data file should be loaded to a work table (GLOBAL TEMP WORK) that has 1 column.
    The table must be a temp table that exists only in the context of the thread that
    it is in, so that the new driver program may be called asynchronously.  Here we are doing
    an INSERT for the data and the CONFIG files...in DB2, the would be just a straight load
    
    */
    
    INSERT INTO #Data_File(Col1)
    --      12345678901234567890123456789012345678901234567890123456789012345678901234567890
    SELECT '12345.678xxxabc111223333zzzyyywwwzzxyzKaiser    Brett     6 Undercliff Terrace ' UNION ALL
    SELECT '12345.678xxxabc123456789zzzyyywwwzzxyzObama     Hussien   666 Pennsylvania Ave ' UNION ALL
    SELECT '12345.678xxxabc987654321zzzyyywwwzzxyzBush      George    123 Texas Ranch      '
    
    /*
    Config File
    
    The Config File will be used to find the offsets of the data, and be used to generate 
    SQL Statements that be used in set based processing to create a scrambled unload file.
    
    */
    
    /*
    [CONFIG TYPE]	[START]		[LENGTH]
    
    The Config table must also only exist in the context of the thread so the driver
    can be used at any time.  We will use offsets here, but there is a predefined layout
    for the config file so the data can be loaded into it's own columns.  Reference the 
    CONFIG FILE CREATION document. 
    
    SSN			16			9
    LNAME			39			10
    FNAME			49			10
    ADDR1			59			22
    */
    
    CREATE TABLE #CONFIG (Col1 varchar(8000))
    GO
    
    INSERT INTO #CONFIG (Col1)	
    --      123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890
    SELECT '                            SSN   00016009' UNION ALL
    SELECT '                            LNAME 00039010' UNION ALL
    SELECT '                            FNAME 00049010'
    GO
    
    /*
    Scrambled Tables
    
    These tables must be created using the existing scrambling Model
    An Insert option or Unload Option must be available to a new driver
    to either Create a loadable flat file, or INSERT the old and new values
    to the table names listed below.
    
    The flat file driver would be used to seed the tables
    The INSERT option would be used to process New Data values, that do not 
    yet appear in the table(s)
    */
    
    -- SSN
    
    CREATE TABLE SSN (SSN varchar(256), SCRAM_SSN varchar(256))
    GO
    
    -- SSN					SCRAM_SSN
    INSERT INTO SSN(SSN, SCRAM_SSN)
    SELECT '111223333',			'132313231'	UNION ALL
    SELECT '123456789',			'291846573'	UNION ALL
    SELECT '987654321',			'192837465'
    GO
    
    --LNAME
    
    CREATE TABLE LNAME (LNAME varchar(256), SCRAM_LNAME varchar(256))
    GO
    
    -- LNAME					SCRAM_LNAME
    INSERT INTO LNAME(LNAME, SCRAM_LNAME)
    SELECT 'Kaiser',				'Lopez'	UNION ALL
    SELECT 'Obama',				'Lee'		UNION ALL
    SELECT 'Bush',				'Luchow'
    GO
    
    -- FNAME
    
    CREATE TABLE FNAME (FNAME varchar(256), SCRAM_FNAME varchar(256))
    GO
    
    
    -- FNAME					SCRAM_FNAME
    INSERT INTO FNAME(FNAME, SCRAM_FNAME)
    SELECT 'Brett',				'Marc'	UNION ALL
    SELECT 'Hussien',				'Young'	UNION ALL
    SELECT 'George',				'Dieter'
    GO
    
    
    /*
    
    Using the #CONFIG TABLE we can genertae the SQL statement to create a 
    DB2 PARMS('SQL') Unload file
    
    */
    
    SELECT SQL FROM (
    SELECT 'SELECT *' AS SQL, 1 AS SQL_Group
    UNION ALL
    SELECT 'FROM #Data_File d' AS SQL, 2 AS SQL_Group
    UNION ALL
    SELECT 'LEFT JOIN ' + CONFIG_CODE 
    					+ ' ON ' + CONFIG_CODE + ' = SUBSTRING(d.Col1,'
    					+ CONFIG_LOCATION
    					+ ','
    					+ CONFIG_LENGTH
    					+ ')' AS SQL, 3 AS SQL_Group
    FROM (
    		SELECT SUBSTRING(Col1,29,6) AS CONFIG_CODE
    			 , SUBSTRING(Col1,35,5) AS CONFIG_LOCATION
    			 , SUBSTRING(Col1,40,3) AS CONFIG_LENGTH
    		  FROM #CONFIG
    ) AS XXX
    ) AS XXX
    
    -- Which Produces
    
    SELECT *
    FROM #Data_File d
    LEFT JOIN SSN    ON SSN    = SUBSTRING(d.Col1,00016,009)
    LEFT JOIN LNAME  ON LNAME  = SUBSTRING(d.Col1,00039,010)
    LEFT JOIN FNAME  ON FNAME  = SUBSTRING(d.Col1,00049,010)
    
    /*  The above statement gives us all we need to create a scrambled file.  
    We would just need to create to SELECT Statement to be a series of offsets to match 
    the input and replace the existing data with the scrambled fields.
    
    This set based method, while it may scan, will still be much faster than any I/O
    intensive method that could be built with CURSORS or Sequential reads through a 
    QSAM File.
    
    In a client server model, all of these statements can be generated.  We can do the same
    In DB2, but this method would require us to have the ability to do dynamic SQL 
    
    */
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  6. #6
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    One thing I try to make sure developers do when they do put dynamic sql in a program is that they check to see if a flag is on or off, in some code table. If the flag is on, then insert the dynamic query to a dynamic query table. This is just so that you can easily look at exactly what your program generated and correct it if need be. Also, allows the DBA to see what is being generated for researching indexes and thei usage.

    Dave Nance

  7. #7
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by dav1mo
    One thing I try to make sure developers do when they do put dynamic sql in a program is that they check to see if a flag is on or off, in some code table. If the flag is on, then insert the dynamic query to a dynamic query table. This is just so that you can easily look at exactly what your program generated and correct it if need be. Also, allows the DBA to see what is being generated for researching indexes and thei usage.

    Dave Nance

    Good idea...I will have too look into that
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •