Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

SYSIBM.SYSPACKSTMT - DB2 V9 TO V10
Goto page 1, 2  Next
 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
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    Post subject: SYSIBM.SYSPACKSTMT - DB2 V9 TO V10
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    Post subject:
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    Post subject:
Reply with quote

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

Site Director


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

PostPosted: Mon Oct 28, 2013 6:50 pm    Post subject:
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    Post subject:
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

Site Director


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

PostPosted: Mon Oct 28, 2013 8:45 pm    Post subject:
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: 1278
Location: Belgium

PostPosted: Mon Oct 28, 2013 9:15 pm    Post subject:
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    Post subject:
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

Site Director


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

PostPosted: Mon Oct 28, 2013 10:18 pm    Post subject:
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: 1738
Location: Bloomington, IL

PostPosted: Mon Oct 28, 2013 10:37 pm    Post subject:
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: 1278
Location: Belgium

PostPosted: Tue Oct 29, 2013 1:09 pm    Post subject:
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    Post subject:
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

Active Member


Joined: 07 Feb 2009
Posts: 982
Location: Oostende, Belgium

PostPosted: Tue Oct 29, 2013 1:59 pm    Post subject:
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    Post subject:
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    Post subject:
Reply with quote

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

Active Member


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

PostPosted: Wed Oct 30, 2013 8:47 pm    Post subject:
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    Post subject:
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    Post subject:
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: 1712
Location: UK

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

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

Active Member


Joined: 01 Dec 2006
Posts: 645
Location: Pennsylvania

PostPosted: Mon Nov 04, 2013 10:45 pm    Post subject:
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
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Goto page 1, 2  Next
Page 1 of 2

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Can not find SQL statement of package... chaoj DB2 2 Wed Mar 11, 2015 12:00 pm
No new posts -551 on SYSIBM.SYSTABLES, SYSIBM.SYST... baramesh DB2 5 Mon Dec 23, 2013 6:29 pm
No new posts Copy Pending status flag in the SYSIB... pmvino DB2 1 Tue May 24, 2011 12:34 pm
No new posts Need DATE from DB2 SYSIBM.SYSDUMMY1 subbu1522 DB2 5 Thu Feb 17, 2011 8:23 pm
No new posts Clarification regarding SYSIBM.SYSDUM... dick scherrer DB2 5 Thu Dec 02, 2010 11:37 am


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us