IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

SYSIBM.SYSPACKSTMT - DB2 V9 TO V10


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
gylbharat

Active Member


Joined: 31 Jul 2009
Posts: 565
Location: Bangalore

PostPosted: Fri Oct 25, 2013 3:36 pm
Reply with quote

Hi,

We have migrated to DB2 v10, and found that there are some changes in this catalog table.

A new column STATEMENT has been added to store the package statements.
in v9, there was a column STMT to store package statements.

V9 - STMT VARCHAR(3500) FOR BIT DATA
v10 - STATEMENT CLOB(2M)

Is there a way I can convert "STATEMENT CLOB(2M)" to " STMT VARCHAR(3500) FOR BIT DATA" ?
Back to top
View user's profile Send private message
gylbharat

Active Member


Joined: 31 Jul 2009
Posts: 565
Location: Bangalore

PostPosted: Fri Oct 25, 2013 5:06 pm
Reply with quote

This is required as we have a inhouse tool to generate DBRM from DB2 catalog. It uses some D2C and C2D rexx functions to generate the DBRM and it requires the input in STMT format- VARCHAR(3500) FOR BIT DATA
Back to top
View user's profile Send private message
gylbharat

Active Member


Joined: 31 Jul 2009
Posts: 565
Location: Bangalore

PostPosted: Mon Oct 28, 2013 4:23 pm
Reply with quote

Did any one got a chance to review this?
Back to top
View user's profile Send private message
dick scherrer

Moderator Emeritus


Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix

PostPosted: Mon Oct 28, 2013 6:50 pm
Reply with quote

Hello,

Quote:
This is required as we have a inhouse tool to generate DBRM from DB2 catalog


If your organization has written this tool, can the organization not change the tool to work correctly in the new environment?

If the tool is purchased, can the vendor not make the change. Sounds like anyone usiong this would need the same change/fix?
Back to top
View user's profile Send private message
gylbharat

Active Member


Joined: 31 Jul 2009
Posts: 565
Location: Bangalore

PostPosted: Mon Oct 28, 2013 7:30 pm
Reply with quote

Its a in house development tool... that's why I am asking how do we convert the new format to the old format.
Back to top
View user's profile Send private message
dick scherrer

Moderator Emeritus


Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix

PostPosted: Mon Oct 28, 2013 8:45 pm
Reply with quote

Hello,

Suggest the code generating the new format has to be changed . . .

What am i missing?
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1281
Location: Belgium

PostPosted: Mon Oct 28, 2013 9:15 pm
Reply with quote

Dick,
db2 generates syspackstmt during bind.
Any home-written tool working for v9 doesn't work with v10.

Instead of "converting" v10 to V9 so you can use the old tool, it would make more sense to rewrite the tool to cope with v10.
Back to top
View user's profile Send private message
gylbharat

Active Member


Joined: 31 Jul 2009
Posts: 565
Location: Bangalore

PostPosted: Mon Oct 28, 2013 9:47 pm
Reply with quote

Hi Guyc,

Is there any way to convert v10 catalog to v9 using SQL? As i think that would be more easier. As i dont know how change the tool to cope with DB2 v10.
Back to top
View user's profile Send private message
dick scherrer

Moderator Emeritus


Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix

PostPosted: Mon Oct 28, 2013 10:18 pm
Reply with quote

Hi Guy,

Quote:
it would make more sense to rewrite the tool to cope with v10.

Yup, that is what i was suggesting, though not worded so well . . .

Glad you're here again for a while.

d
Back to top
View user's profile Send private message
Akatsukami

Global Moderator


Joined: 03 Oct 2009
Posts: 1788
Location: Bloomington, IL

PostPosted: Mon Oct 28, 2013 10:37 pm
Reply with quote

gylbharat wrote:
Is there any way to convert v10 catalog to v9 using SQL? As i think that would be more easier. As i dont know how change the tool to cope with DB2 v10.

What language is the tool written in that you cannot cope with it?
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1281
Location: Belgium

PostPosted: Tue Oct 29, 2013 1:09 pm
Reply with quote

I no longer have access to a db2 v9 or any db2 for that matter.
So I cannot help you icon_sad.gif
Back to top
View user's profile Send private message
gylbharat

Active Member


Joined: 31 Jul 2009
Posts: 565
Location: Bangalore

PostPosted: Tue Oct 29, 2013 1:24 pm
Reply with quote

The tool is written in REXX.

There is a cursor in REXX to select the STMT column and it uses some C2D and D2C functions to convert it into DBRM format. Now with v10, the column is CLOB and I am not sure what changes has to be made in REXX to convert it into DBRM.
Back to top
View user's profile Send private message
prino

Senior Member


Joined: 07 Feb 2009
Posts: 1306
Location: Vilnius, Lithuania

PostPosted: Tue Oct 29, 2013 1:59 pm
Reply with quote

A CLOB is (in essence) nothing more than a very, very, very long VARCHAR, as you might have realized if you had looked up what they are, e.g. on Wikipedia
Back to top
View user's profile Send private message
gylbharat

Active Member


Joined: 31 Jul 2009
Posts: 565
Location: Bangalore

PostPosted: Tue Oct 29, 2013 4:26 pm
Reply with quote

yes i know... but the only issue is... earlier in v9, the data format was VARCHAR(3500) for BIT DATA. so every line had some length information then followed by data in BIT.

But now in v10, its CLOB(2M) as SBCS data, which means now there is no length information in the beginning of the row. So now I dont know how to use new format to generate the DBRM.

Existing code snippet as per DB2 v9:

get the row for each package from the SYSPACKSTMT

Code:
    /* GET PACKAGE STATEMENTS */                         
    STMT = STMT "  STMTNO, SECTNO, SEQNO, "               
    STMT = STMT "  STMT "                                 
    STMT = STMT " FROM SYSIBM.SYSPACKSTMT"               
    STMT = STMT " WHERE COLLID = '"PLNAME"' "             
    STMT = STMT " AND NAME = '"DBRMNAME"' "               
    IF F = 'Y' THEN                                       
     DO                                                   
      STMT = STMT " AND VERSION = '"VERSIONC"' "         
     END                                                 
    STMT = STMT  "ORDER BY 3"                             
  END       



Code:

/*                                                                */   
/* GET ALL OF THE SQL STATEMENTS FOR A SINGLE DBRM                */   
/*                                                                */   
  DO STMTLOOP = 0 WHILE (SQLCODE >= 0) & (SQLCODE <> 100)               
/*                                                                */   
/* FETCH THE 254 BYTE SEGEMENTS OF STATEMENTS, THEN WRITE OUT     */   
/* EACH STATEMENT.                                                */   
/*                                                                */   
    "EXECSQL FETCH C2 INTO :STMTNO, :SECTNO, :SEQNO, :TEXT"             
    IF SQLCODE < 0 THEN CALL SQLCA                                     
    IF SQLCODE = 0 THEN                                                 
    DO                                                                 
/*                                                                */   
/* FOR THE FIRST RECORD IN THE DBRM, WE PRODUCE THE DBRM HEADER   */   
/* RATHER THAN A DBRM STATEMENT ENTRY.                            */   
/*                                                                */   
      IF STMTNO = 0 THEN                                               
      DO                                                               
        DBRMTYPE = SUBSTR(TEXT,43,1)/*43+4+4+8+8+8*/                   
        IF (DBRMTYPE = '00'X | DBRMTYPE = '40'X) THEN                   
        TOTLEN = 80                                                     
        ELSE                                                           
        TOTLEN = 160                                                   
        DBRM =         "DBRM"                                           
        DBRM = DBRM || D2C(TOTLEN,4)                                   
        DBRM = DBRM || SUBSTR(PLCREATOR,1,8,' ')                       
        DBRM = DBRM || SUBSTR(DBRMNAME,1,8,' ')                         
        DBRM = DBRM || SUBSTR(TIMESTAMP,1,8,' ')                       
        DBRM = DBRM || TEXT                                             
        DBRM = DBRM || D2C(LENGTH(VERSION),2)                           
        DBRM = DBRM || SUBSTR(VERSION,1,64,' ')                         
        DBRM = DBRM || COPIES('00'X,13)                                 
        DBRM = DBRM || D2C(0,1)                                         
        DO I = 1 TO TOTLEN BY 80                                       
          DBRM1=SUBSTR(DBRM,I,80,' ')                                   
          ADDRESS TSO "EXECIO 1 DISKW DBRMLIB(STEM DBRM"               
        END                                                             
      END                                                               
      ELSE                                                             
/*                                                                */   
/* FOR ALL OF THE STATEMENT RECORDS, GET THE DATA FROM THE        */   
/* CATALOG AND RECONSTRUCT THE DBRM STATEMENT ENTRY, INCLUDING    */   
/* THE STATEMENT TEXT AND PVARS.                                  */   
/*                                                                */   
      DO                                                               
        TOTLEN = C2D(SUBSTR(TEXT,1,4))                                 
        SUMLEN = LENGTH(TEXT)-4                                         
/*                                                                */   
/* START WITH THE EYE CATCHER                                     */   
/*                                                                */   
        DBRM =         "DBRM"                                           
/*                                                                */   
/* THERE ARE TWO FORMATS FOR THE DBRM, AND WE WILL WORK ON BOTH   */   
/* DBRMTYPE IS THE INTERNAL FLAG, BUILD DBRMPRT TO BE PRINTABLE.  */   
/*                                                                */   
        DBRMPRT = "NEW FORMAT"                                         
        IF (DBRMTYPE = '00'X | DBRMTYPE = '40'X) THEN                   
        DO                                                             
        DBRMPRT = "OLD FORMAT"                                         
        DBRM = DBRM || D2C(TOTLEN+12,4)                                 
        DBRM = DBRM || D2C(SECTNO,2)                                   
        DBRM = DBRM || D2C(STMTNO,2)                                   
        DBRM = DBRM || SUBSTR(TEXT,5,LENGTH(TEXT)-4)                   
        END                                                             
        ELSE                                                           
        DO                                                             
        DBRM = DBRM || D2C(TOTLEN+20,4)                                 
        DBRM = DBRM || D2C(SECTNO,2)                                   
        DBRM = DBRM || D2C(0,2)                                         
        DBRM = DBRM || D2C(STMTNO,4)                                   
        DBRM = DBRM || D2C(0,2)                                         
        DBRM = DBRM || D2C(20,2)                                       
        DBRM = DBRM || SUBSTR(TEXT,5,LENGTH(TEXT)-4)                   
        END                                                             
        DO WHILE (SQLCODE >= 0) & (SQLCODE <> 100) & (SUMLEN < TOTLEN) 
          DO I = 1 TO LENGTH(DBRM) BY 80                               
            DBRM1=SUBSTR(DBRM,I,MIN(80,LENGTH(DBRM)-I+1))               
            IF (LENGTH(DBRM1) = 80) THEN                               
              ADDRESS TSO "EXECIO 1 DISKW DBRMLIB(STEM DBRM"           
          END                                                           
          IF (LENGTH(DBRM1) <> 80) THEN                                 
            DBRM=DBRM1                                                 
          ELSE                                                         
            DBRM=""                                                     
          "EXECSQL FETCH C2 INTO :STMTNO, :SECTNO, :SEQNO, :TEXT"       
          IF SQLCODE < 0 THEN CALL SQLCA                               
          IF SQLCODE = 0 THEN                                           
          DO                                                           
            DBRM = DBRM||TEXT                                           
            SUMLEN = SUMLEN+LENGTH(TEXT)                               
          END                                                           
        END                                                             
        DO I = 1 TO LENGTH(DBRM) BY 80                                 
          DBRM1=SUBSTR(DBRM,I,80,' ')                                   
          ADDRESS TSO "EXECIO 1 DISKW DBRMLIB(STEM DBRM"               
        END                                                             
      END                                                               
    END                                                                 
  END STMTLOOP     
Back to top
View user's profile Send private message
gylbharat

Active Member


Joined: 31 Jul 2009
Posts: 565
Location: Bangalore

PostPosted: Wed Oct 30, 2013 6:52 pm
Reply with quote

Any ideas?
Back to top
View user's profile Send private message
don.leahy

Active Member


Joined: 06 Jul 2010
Posts: 765
Location: Whitby, ON, Canada

PostPosted: Wed Oct 30, 2013 8:47 pm
Reply with quote

If I were you, the first thing I would confirm is whether DSNREXX supports CLOB. Check the DB2 Application Programming Guide.

It probably does now, but a few years ago there was no CLOB support.
Back to top
View user's profile Send private message
gylbharat

Active Member


Joined: 31 Jul 2009
Posts: 565
Location: Bangalore

PostPosted: Fri Nov 01, 2013 1:55 pm
Reply with quote

yes It does support Clob. I am able to select the data from clob column but not able to figure out... how to convert it into DBRM format.
Back to top
View user's profile Send private message
gylbharat

Active Member


Joined: 31 Jul 2009
Posts: 565
Location: Bangalore

PostPosted: Mon Nov 04, 2013 9:05 pm
Reply with quote

Does any one has an idea how DBRM can be generated using new format?
Back to top
View user's profile Send private message
Nic Clouston

Global Moderator


Joined: 10 May 2007
Posts: 2455
Location: Hampshire, UK

PostPosted: Mon Nov 04, 2013 10:06 pm
Reply with quote

Obviously not
Back to top
View user's profile Send private message
daveporcelan

Active Member


Joined: 01 Dec 2006
Posts: 792
Location: Pennsylvania

PostPosted: Mon Nov 04, 2013 10:45 pm
Reply with quote

gylbharat,

This is the Third time you have re-asked for an answer when nobody responds to a previous query.

Do you realize this make you look desperate, needy, and greedy?

What you are trying to do is modify an INHOUSE tool that is performing a very unconventional task.

A DBRM is created from Compiling SOURCE Code, not re-engineering it from the SYSIBM.SYS... table.

While that is a neat tool, I would not rely on such a DBRM for much. The Source code is really needed.

When you successfully modify this tool, you will be paid for it. Asking three times for HELP when you will get PAID, makes you look greedy.
Back to top
View user's profile Send private message
don.leahy

Active Member


Joined: 06 Jul 2010
Posts: 765
Location: Whitby, ON, Canada

PostPosted: Tue Nov 05, 2013 12:13 am
Reply with quote

I have heard that some shops, (not the one that I work at) make no effort to retain the DBRM after the package has been bound.

Technically, once the package is bound the DBRM is no longer needed. If you need to Bind the package into another collection within the same DB2 subsystem, you can do a BIND copy.

Despite that, IMO the DBRM should be treated like gold, and should be managed in parallel with the underlying source code. That is the practice in the shop where I work, and I have never felt the need to recover the DBRM text from the catalog, even with older versions of DB2 where it was relatively easy to do.
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1281
Location: Belgium

PostPosted: Tue Nov 05, 2013 8:41 pm
Reply with quote

Quote:
Database request modules
The database request module (DBRM) is a data set that contains the SQL statements and host variable information that is extracted from the source program, along with information that identifies the program and ties the DBRM to the translated source statements. It becomes the input to the bind process.
The data set requires space to hold all the SQL statements plus space for each host variable name and some header information. The header information alone requires approximately two records for each DBRM, 20 bytes for each SQL record, and 6 bytes for each host variable.

For an exact format of the DBRM, see the DBRM mapping macros, DSNXDBRM and DSNXNBRM, in library prefix.SDSNMACS. The DCB attributes of the data set are RECFM FB, LRECL 80. The precompiler sets the characteristics. You can use IEBCOPY, IEHPROGM, TSOCOPY and DELETE commands, or other PDS management tools for maintaining these data sets.

Restriction: Do not modify the contents of the DBRM. If you do, unpredictable results can occur. DB2 does not support modified DBRMs.

In a DBRM, the SQL statements and the list of host variable names use the UTF-8 character encoding scheme.

All other character fields in a DBRM use EBCDIC. The current release marker (DBRMMRIC) in the header of a DBRM is marked according to the release of the precompiler, regardless of the value of NEWFUN.


publib.boulder.ibm.com/infocenter/dzichelp/v2r2/topic/com.ibm.db2z10.doc.apsg/src/tpc/db2z_outputprecompiler.htm
Back to top
View user's profile Send private message
Akatsukami

Global Moderator


Joined: 03 Oct 2009
Posts: 1788
Location: Bloomington, IL

PostPosted: Tue Nov 05, 2013 8:51 pm
Reply with quote

I presume Bind Manager is a pay-for-play option?
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Similar Topics
Topic Forum Replies
No new posts Discrepancy b/w SYSIBM tables and BMC... DB2 0
No new posts SYSIBM Tables Query DB2 8
No new posts Column names in SYSIBM tables DB2 5
No new posts Create view statement from SYSIBM.SYS... DB2 7
No new posts SYSIBM Catlog tables to see DDL chang... DB2 2
Search our Forums:

Back to Top