Blog
DB2utor

Categories

February 10, 2009

Viewing a UNICODE DBRM in an EBCDIC Browser

Sometimes you need to look at the contents of a database request module (DBRM), which is typically stored as a member in a PDS file on z/OS. See the "Preparing an Application Program to Run" section of the "DB2 for z/OS Application Programming and SQL Guide" for more about DBRMs.

Most data on the z/OS platform is stored in an EBCDIC format. When you browse or edit a file using the ISPF editor, it's displayed to the screen based on the EBCDIC character set. However, as of DB2 UDB for z/OS Version 8, the DBRM is stored in UNICODE UTF-8 format. This is a real problem for mainframe developers who need to look at the DBRM.

Here's how some SQL in a COBOL source program appears when the DBRM is being browsed:

COBOL Source Code

 000694                 EXEC SQL DECLARE TBL_WC1_CS  CURSOR FOR                
 000695                     SELECT CREATOR                                     
 000696                          , NAME                                        
 000697                          , DBNAME                                      
 000698                          , TSNAME                                      
 000699                          , COLCOUNT                                    
 000700                     FROM SYSIBM.SYSTABLES T                            
 000701       *             WHERE CREATOR = :WS-IN-TBCREATOR                   
 000702       *               AND NAME LIKE :WS-IN-TBNAME                      
 000704       *               AND TYPE = 'T'                                   
 000705                     ORDER BY CREATOR                                   
 000706                            , NAME                                      
 000707                     WITH UR                                            


Now here's the DBRM for the above code. I have HEX on so you can see the character numeric values:

DBRM...x.......×.......»àáä< êá.èâ<¬ïä.¬äë.äíêë|ê.ã|ê.ëá<áäè.äêá è|ê...+ (á...àâ
CCDD000A0000000B0001000844444542544554354524555452445254444524544545222444422244
429400070100003F0004000B453C125042CF731F3303523F206F2035C534032514F20C0E1D50C042
 ------------------------------------------------------------------------------
+ (á...èë+ (á...ä|<ä|í+è.ãê|(.ëßëñâ(...ëßëè â<áë.è.|êàáê.âß.äêá è|ê...+ (á.ïñèç.
44442225544442224444454524544255544422255554444525245445245245445452224444254542
E1D50C043E1D50C03FC3F5E4062FD039392D0E0393412C53040F2452029032514F20C0E1D5079480
 ------------------------------------------------------------------------------
íê.....                                                                        
55200004444444444444444444444444444444444444444444444444444444444444444444444444
52000000000000000000000000000000000000000000000000000000000000000000000000000000
 ------------------------------------------------------------------------------

So, my question: Does the existing DBRM contain the lines of code commented out above? You may not know this, but the ISPF editor has some enhancements that allow you to convert EBCDIC characters to other formats, including UTF-8.

While you're browsing the data set, simply enter the DISPLAY UTF8 command. The above DBRM will be translated to what you see below.

......................DECLARE TBL_WC1_CS CURSOR FOR SELECT CREATOR , NAME , DB 
CCDD000A0000000B0001000844444542544554354524555452445254444524544545222444422244
429400070100003F0004000B453C125042CF731F3303523F206F2035C534032514F20C0E1D50C042
 ------------------------------------------------------------------------------
NAME , TSNAME , COLCOUNT FROM SYSIBM . SYSTABLES T ORDER BY CREATOR , NAME WITH
44442225544442224444454524544255544422255554444525245445245245445452224444254542
E1D50C043E1D50C03FC3F5E4062FD039392D0E0393412C53040F2452029032514F20C0E1D5079480
 ------------------------------------------------------------------------------
UR ....@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
55200004444444444444444444444444444444444444444444444444444444444444444444444444
52000000000000000000000000000000000000000000000000000000000000000000000000000000
 ------------------------------------------------------------------------------
.....&..............ÄECLARE TBL_WC2_CS CURSOR FOR SELECT CREATOR , NAME , DB   
CCDD00020000000C0001000C44444542544554354524555452445254444524544545222444422244
429400160200003F00040003453C125042CF732F3303523F206F2035C534032514F20C0E1D50C042
 ------------------------------------------------------------------------------

The character numeric values haven't changed, just the display. So you cannot do a find on 'TBL_WC1_CS'; instead, you must do a find on X'452054424C5F'. Notice after the FROM clause I don't have a WHERE clause before the ORDER BY clause. This DBRM matches the SQL in the COBOL program above.

Learn more about the ISPF Browse Display command here.