Results 1 to 11 of 11
  1. #1
    Join Date
    Aug 2012
    Posts
    45

    Unanswered: SQL to parse binary and packed decimal

    Greetings all. We are using DB2 9.7 on z/OS, and users have DB2 for LUW 9.7 installed on Windows 7 32 bit machines. There is one column of data that is defined as VARCHAR, and the CCSID is 0, so I am fairly certain this is binary data. The field in question is a structured array of elements that make up the components of an account balance. We currently use HEX() to convert it, then use SUBSTR() to pull out the elements and sum them accordingly. I am just wondering if there is an easier way. I don't have the ability to do much on the mainframe other than query it, so what I can do is limited. I hope the below example is enough to illustrate what I am trying to do.

    Code:
    /*	In the accounts table there is a field for the account id, and a field of binary data
    	which contains an array of any number of bytes with a length of 7 each.  So, for example,
    	in the below 'ACCOUNT_TABLE' CTE, each 'HEX_VAL' string is a HEX representation of 
    	the data, each of which contains 3 elements in the array.  The first two characters of
    	the hex element represent a code (can be A through M), and characters 3 through 14
    	are (binary bytes 2-7) are packed decimal.  I can use HEX() and a numbers table to
    	split out the elements, and SUBSTR() to parse out the code and packed decimal part,
    	but I am wondering if in DB2 SQL there is any way to directly convert the packed 
    	decimal without having to first convert to hex then use SQL as I am doing below.  My
    	numbers table is indexed, so this works pretty well, but some folks here don't get
    	it, so I am looking for an easier way.
    	
    	The first one , 'C200000012400CD400000001625CD500000000998C' breaks down to...
    		C2	00000012400C 
    		D4	00000001625C
    		D5	00000000998C
    	and would sum to 150.23
    	
    	Both CTE's below are just representations of the data.  In reality, both are permanent
    	tables, and the field could contain up to 8 elements, but I am only showing 3. 
    	
    	Could the SQL maybe be put into a UDF?
    */
    
    WITH ACCOUNT_TABLE (ID, HEX_VAL) AS
    	(SELECT 160002,  'C200000012400CD400000001625CD500000000998C' FROM SYSIBM.SYSDUMMY1 UNION
    	 SELECT 161885,  'C200000004367CC300000003700CC400000001500C' FROM SYSIBM.SYSDUMMY1 UNION
    	 SELECT 350363,  'C200000023200CD400000006390DD500000001394C' FROM SYSIBM.SYSDUMMY1 UNION
    	 SELECT 433110,  'C200000013000CD400000019205DD500000004036C' FROM SYSIBM.SYSDUMMY1 UNION
    	 SELECT 436329,  'C200000007491CC300000004400CD500000000295C' FROM SYSIBM.SYSDUMMY1 UNION
    	 SELECT 450411,  'C200000018200CD400000000569CD500000000499C' FROM SYSIBM.SYSDUMMY1 UNION
    	 SELECT 693309,  'C400000003733DC600000010000DD400000007143C' FROM SYSIBM.SYSDUMMY1 UNION
    	 SELECT 940007,  'C200000024192CD400000001455DD500000001792C' FROM SYSIBM.SYSDUMMY1 UNION
    	 SELECT 942508,  'C200000019600CD400000026038CD500000001993C' FROM SYSIBM.SYSDUMMY1 UNION
    	 SELECT 1684869, 'C200000021755CC700000006124DD400000009148D' FROM SYSIBM.SYSDUMMY1),
    	 NUMBERS_TABLE (N) AS
    	 (SELECT 1 FROM SYSIBM.SYSDUMMY1 UNION ALL
    	  SELECT N + 14 FROM NUMBERS_TABLE
    	   WHERE N < 160)
    SELECT
    	ID, 
    	SUM(CASE WHEN CODE IN ('C2','C4','C5','C6','D4','D5') 
    			 THEN VAL
    		     ELSE 0.00
    	     END *
    	    CASE WHEN SIGN IN ('B','D') THEN -1 ELSE 1 END) AS BAL
      FROM 
      (		
    	SELECT 
    		a.ID, 
    		SUBSTR(HEX_VAL,b.N,2) AS CODE,
    		CAST(SUBSTR(HEX_VAL,b.N + 2,11) AS DECIMAL(11,2))/100.00 AS VAL,
    		SUBSTR(HEX_VAL,b.N + 13,1) AS SIGN
    	  FROM ACCOUNT_TABLE a, NUMBERS_TABLE b
    	 WHERE b.n < LENGTH(a.HEX_VAL)
    	) t1
     GROUP BY ID;
    Thank you

    Greg

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by gsnidow View Post
    I am just wondering if there is an easier way.
    Define "easier".
    ---
    "It does not work" is not a valid problem statement.

  3. #3
    Join Date
    Aug 2012
    Posts
    45
    Ah, yes, I should have been more clear. The problem is that a junior analyst does not always easily grasp the concept of joining to a numbers table and using it to step through a string and pull out some values. In addition to their working on what already may be a complex query, if they need to include an account balance, they now have to work in the code I've posted above. All of us connect to DB2 using an application called Razor SQL, which just provides an ODBC connection. We don't have the ability to use variables, temp tables, or anything like that. However, I've found that in the object explorer, I see a database with my network name. In that database I can create any tables, indexes, and I was able to create and use the TAN() function that the IBM site gives as an example. All of these objects exist in the database with my network name (and I'm not even sure what you call this database). So, my thought was that I could write a function that I could have all analysts create, and it would exist in the database with their network name. In that case, assuming the function was called Account_balance(), instead of the SQL I posted above, they could simply call the function in their SQL and pass in the binary field as it is, or by using HEX first, like this...

    SELECT id, Account_balance(HEX(binary_field))
    FROM ...

    I hope this helps explain what I mean by 'easier'. Thank you.

    Greg

  4. #4
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    You cannot define a function in one (local) database and use it in a query against a different (remote) database, so if I understand your idea correctly, it is not going to work. You will need to create that function (or a view implementing your logic) in the remote database.
    ---
    "It does not work" is not a valid problem statement.

  5. #5
    Join Date
    Aug 2012
    Posts
    45
    Well, I am sure the database with my network name that shows up in the object explorer is on the host. For example, if my network name is 'joeuser',
    then if I execute this from my Razor SQL...

    CREATE TABLE joeuser.test (Col1 CHAR(1))

    ... I can then use that table in a query including other schemas. This executes with no error...

    SELECT
    a.SomeColumn
    FROM ProductionSchema.SomeTable a INNER JOIN joeuser.Test b
    ON a.Col1 = b.Col1.

    So, my thought is the table is being created in some kind of user space within DB2 on the host server. With this in mind, I was hoping to be able to write a function and just have every user execute the code to create the function in their own user space. Does this make sense?

  6. #6
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    We are using DB2 9.7 on z/OS
    Is it right?
    I saw DB2 Version 9.1, DB2 10, DB2 11 for z/OS in
    IBM Information Management Software for z/OS Solutions Information Center
    But, no DB2 9.7 on z/OS.


    By reading manuals(Information Center),
    I thought the followings:
    If you are using DB2 10 for z/OS, it may be easy to create a function from your sample SELECT statement on the DB2 (for z/OS).
    If you are using DB2 Version 9.1 for z/OS, it may be not easy to create a function by the similar way on DB2 10 for z/OS.

  7. #7
    Join Date
    Aug 2012
    Posts
    45
    Ok, I did not realize there was a difference in versions between LUW and z/OS, so I apologize for the confusion. The version on the host is DSN09015, which, if google is working, is 'DB2 for z/OS Version 9.1 in new-function mode'.

  8. #8
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    The version on the host is DSN09015, which, if google is working, is 'DB2 for z/OS Version 9.1 in new-function mode'.
    So, creating a view implementing your logic(suggested by n_i) might be a solution.

    The reason I thought
    If you are using DB2 Version 9.1 for z/OS, it may be not easy to create a function by the similar way on DB2 10 for z/OS.
    was that you can't return subselect nor scalar fullselect in an SQL function on DB2 Version 9.1 for z/OS.

  9. #9
    Join Date
    Aug 2012
    Posts
    45
    Thank you tonkuma. One of the DBA's just told me they are in the process of upgrading to V10, which should be up on production next month. So, I guess at this point I'll just stick with my solution.

  10. #10
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Although you wrote "they are in the process of upgrading to V10, which should be up on production next month",
    I tried to make a function in the restriction of DB2 9.1 for z/OS(mainly for my fun),
    by referencing
    DB2 V9.1 - DB2 Version 9.1 for z/OS Information Center


    The following examples are not tested on DB2 for z/OS.
    So, there may be some syntax error(s) or violation of restriction(s) on DB2 for z/OS.


    Example 1: Create a (sub-)function
    Code:
    CREATE FUNCTION each_account(in_hex_val VARCHAR(14) )
     RETURNS DEC(13,2)
     CONTAINS SQL
     DETERMINISTIC
     NO EXTERNAL ACTION
    RETURN
    CASE
    WHEN SUBSTR(in_hex_val , 1 , 2) IN ('C2' , 'C4' , 'C5' , 'C6' , 'D4' , 'D5') THEN
         CAST( SUBSTR(in_hex_val , 3 , 11) AS DECIMAL(11,2) ) / 100.00
    ELSE 0.00
    END *
    CASE
    WHEN SUBSTR(in_hex_val , 14 , 1) IN ('B' , 'D') THEN
         -1
    ELSE  1
    END
    ;

    Example 2: Create Account_balance function, by using the function created in Example 1.
    Code:
    CREATE FUNCTION Account_balance(in_bin_char_val VARCHAR(56) )
    RETURNS DEC(13,2)
     CONTAINS SQL
     DETERMINISTIC
     NO EXTERNAL ACTION
    RETURN
      each_account( HEX(SUBSTR(in_bin_char_val ,  1 , 7)) )
    + each_account( HEX(SUBSTR(in_bin_char_val ,  8 , 7)) )
    + each_account( HEX(SUBSTR(in_bin_char_val , 15 , 7)) )
    + each_account( HEX(SUBSTR(in_bin_char_val , 22 , 7)) )
    + each_account( HEX(SUBSTR(in_bin_char_val , 29 , 7)) )
    + each_account( HEX(SUBSTR(in_bin_char_val , 36 , 7)) )
    + each_account( HEX(SUBSTR(in_bin_char_val , 43 , 7)) )
    + each_account( HEX(SUBSTR(in_bin_char_val , 50 , 7)) )
    ;
    Note: I coded up to 8 elements.
    Because, you wrote...
    Code:
    ...
    	Both CTE's below are just representations of the data.  In reality, both are permanent
    	tables, and the field could contain up to 8 elements, but I am only showing 3.
    But, if saw this code, the column might be able to contain up to 12 elements.
    Code:
    ...
    	 NUMBERS_TABLE (N) AS
    	 (SELECT 1 FROM SYSIBM.SYSDUMMY1 UNION ALL
    	  SELECT N + 14 FROM NUMBERS_TABLE
    	   WHERE N < 160)
    ...

  11. #11
    Join Date
    Aug 2012
    Posts
    45
    Thank you so much tonkuma, it works like a charm. I never would have thought of breaking it up into two function like that. I'm mostly familiar with TSQL, where you can basically do what ever you need to with the SQL within a function. The reason the numbers table has 160 is because the binary column is 80 bytes. I don't know if the extra space is a vestige from the past, or maybe is there to accommodate things that happen during nightly batch processing, and I did not want to risk my code failing because my numbers table was not large enough. All I know is that out of 30M records going back to 1996, the longest value has 8 account elements. Anyhow, thank you so much, you've given me lots to think about.

    Greg

Posting Permissions

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