View previous topic :: View next topic
|
Author |
Message |
gylbharat
Active Member
Joined: 31 Jul 2009 Posts: 565 Location: Bangalore
|
|
|
|
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 |
|
|
gylbharat
Active Member
Joined: 31 Jul 2009 Posts: 565 Location: Bangalore
|
|
|
|
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 |
|
|
gylbharat
Active Member
Joined: 31 Jul 2009 Posts: 565 Location: Bangalore
|
|
|
|
Did any one got a chance to review this? |
|
Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006 Posts: 19244 Location: Inside the Matrix
|
|
|
|
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 |
|
|
gylbharat
Active Member
Joined: 31 Jul 2009 Posts: 565 Location: Bangalore
|
|
|
|
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 |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006 Posts: 19244 Location: Inside the Matrix
|
|
|
|
Hello,
Suggest the code generating the new format has to be changed . . .
What am i missing? |
|
Back to top |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009 Posts: 1281 Location: Belgium
|
|
|
|
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 |
|
|
gylbharat
Active Member
Joined: 31 Jul 2009 Posts: 565 Location: Bangalore
|
|
|
|
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 |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006 Posts: 19244 Location: Inside the Matrix
|
|
|
|
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 |
|
|
Akatsukami
Global Moderator
Joined: 03 Oct 2009 Posts: 1788 Location: Bloomington, IL
|
|
|
|
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 |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009 Posts: 1281 Location: Belgium
|
|
|
|
I no longer have access to a db2 v9 or any db2 for that matter.
So I cannot help you |
|
Back to top |
|
|
gylbharat
Active Member
Joined: 31 Jul 2009 Posts: 565 Location: Bangalore
|
|
|
|
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 |
|
|
prino
Senior Member
Joined: 07 Feb 2009 Posts: 1306 Location: Vilnius, Lithuania
|
|
|
|
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 |
|
|
gylbharat
Active Member
Joined: 31 Jul 2009 Posts: 565 Location: Bangalore
|
|
|
|
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 |
|
|
gylbharat
Active Member
Joined: 31 Jul 2009 Posts: 565 Location: Bangalore
|
|
|
|
Any ideas? |
|
Back to top |
|
|
don.leahy
Active Member
Joined: 06 Jul 2010 Posts: 765 Location: Whitby, ON, Canada
|
|
|
|
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 |
|
|
gylbharat
Active Member
Joined: 31 Jul 2009 Posts: 565 Location: Bangalore
|
|
|
|
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 |
|
|
gylbharat
Active Member
Joined: 31 Jul 2009 Posts: 565 Location: Bangalore
|
|
|
|
Does any one has an idea how DBRM can be generated using new format? |
|
Back to top |
|
|
Nic Clouston
Global Moderator
Joined: 10 May 2007 Posts: 2455 Location: Hampshire, UK
|
|
|
|
Obviously not |
|
Back to top |
|
|
daveporcelan
Active Member
Joined: 01 Dec 2006 Posts: 792 Location: Pennsylvania
|
|
|
|
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 |
|
|
don.leahy
Active Member
Joined: 06 Jul 2010 Posts: 765 Location: Whitby, ON, Canada
|
|
|
|
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 |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009 Posts: 1281 Location: Belgium
|
|
|
|
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 |
|
|
Akatsukami
Global Moderator
Joined: 03 Oct 2009 Posts: 1788 Location: Bloomington, IL
|
|
|
|
I presume Bind Manager is a pay-for-play option? |
|
Back to top |
|
|
|