Topic
1 reply Latest Post - ‏2011-09-07T20:27:14Z by JP_Parkin
SystemAdmin
SystemAdmin
203 Posts
ACCEPTED ANSWER

Pinned topic Unpacking a VARCHAR string of data coming from z/OS to LUW

‏2011-09-07T18:56:16Z |
Source: DB2 z/OS 9.1
Target: DB2 LUW 9.5

Trying to unpack a VARCHAR that carries DECIMAL, SMALLINT and CHARACTER data. Thought I could do a simple data flow from Source table to Target for all of the discrete columns. And then for the VARCHAR do the following:

For DECIMAL data where COLVC is defined as VARCHAR() FOR BIT DATA

DEC(SUBSTR(HEX(SUBSTR(SRCE.COLVC,1,8)),1,15),15,0)

For SMALLINT data

SMALLINT(
CASE SUBSTR(HEX(SUBSTR(SRCE.COLVC,70,2)),1,1)
WHEN '0' THEN 0*16*16*16
WHEN '1' THEN 1*16*16*16
WHEN '2' THEN 2*16*16*16
WHEN '3' THEN 3*16*16*16
WHEN '4' THEN 4*16*16*16
WHEN '5' THEN 5*16*16*16
WHEN '6' THEN 6*16*16*16
WHEN '7' THEN 7*16*16*16
WHEN '8' THEN 8*16*16*16
WHEN '9' THEN 9*16*16*16
WHEN 'A' THEN 10*16*16*16
WHEN 'B' THEN 11*16*16*16
WHEN 'C' THEN 12*16*16*16
WHEN 'D' THEN 13*16*16*16
WHEN 'E' THEN 14*16*16*16
WHEN 'F' THEN 15*16*16*16
END +
CASE SUBSTR(HEX(SUBSTR(ZZ_STRG,70,2)),2,1)
WHEN '0' THEN 0*16*16
WHEN '1' THEN 1*16*16
WHEN '2' THEN 2*16*16
WHEN '3' THEN 3*16*16
WHEN '4' THEN 4*16*16
WHEN '5' THEN 5*16*16
WHEN '6' THEN 6*16*16
WHEN '7' THEN 7*16*16
WHEN '8' THEN 8*16*16
WHEN '9' THEN 9*16*16
WHEN 'A' THEN 10*16*16
WHEN 'B' THEN 11*16*16
WHEN 'C' THEN 12*16*16
WHEN 'D' THEN 13*16*16
WHEN 'E' THEN 14*16*16
WHEN 'F' THEN 15*16*16
END +
CASE SUBSTR(HEX(SUBSTR(ZZ_STRG,70,2)),3,1)
WHEN '0' THEN 0*16
WHEN '1' THEN 1*16
WHEN '2' THEN 2*16
WHEN '3' THEN 3*16
WHEN '4' THEN 4*16
WHEN '5' THEN 5*16
WHEN '6' THEN 6*16
WHEN '7' THEN 7*16
WHEN '8' THEN 8*16
WHEN '9' THEN 9*16
WHEN 'A' THEN 10*16
WHEN 'B' THEN 11*16
WHEN 'C' THEN 12*16
WHEN 'D' THEN 13*16
WHEN 'E' THEN 14*16
WHEN 'F' THEN 15*16
END +
CASE SUBSTR(HEX(SUBSTR(ZZ_STRG,70,2)),4,1)
WHEN '0' THEN 0
WHEN '1' THEN 1
WHEN '2' THEN 2
WHEN '3' THEN 3
WHEN '4' THEN 4
WHEN '5' THEN 5
WHEN '6' THEN 6
WHEN '7' THEN 7
WHEN '8' THEN 8
WHEN '9' THEN 9
WHEN 'A' THEN 10
WHEN 'B' THEN 11
WHEN 'C' THEN 12
WHEN 'D' THEN 13
WHEN 'E' THEN 14
WHEN 'F' THEN 15
END) AS COLSM

Actually hoping to use the HEX2SMALLINT() function but that's not (yet?) available on the z/OS side...

For CHARACTER data

SUBSTR(SRCE.COLVC,10,5)

Well, I have not been able to get beyond the first DECIMAL field when executing the data flow (-4474, Illegal conversion -- here's the message from the execution log-- DBWSQWR 2011-08-29 13:31:21 ERROR - JDBCLoad: SQL Exception. SQL state = null; error code = -4474; error Message = jcc108310406" to "java.lang.String" ERRORCODE=-4474, SQLSTATE=null)

Has anyone else had success in "unpacking" a VARCHAR coming from z/OS to LUW?
Updated on 2011-09-07T20:27:14Z at 2011-09-07T20:27:14Z by JP_Parkin
  • JP_Parkin
    JP_Parkin
    128 Posts
    ACCEPTED ANSWER

    Re: Unpacking a VARCHAR string of data coming from z/OS to LUW

    ‏2011-09-07T20:27:14Z  in response to SystemAdmin
    To be certain of the exact condition that is triggering your error, I'd probably need to see the data flow to see the types of the source and target columns that are being used in the conversion and some sample data to ensure that I fully understand your environment. Most likely an invalid numeric representation of a String ( VARCHAR from the zDB2 ) is trying to be converted to a numeric value - but that's just a guess.

    Not sure whether any of this will be helpful, but here's some thoughts on handling this sort of conversion in a data flow. You may also find that different versions of the Design Studio may handle the data type mapping slightly different ( ie. 9.5.x compared with the 9.7.x release ).

    If you would like to make use of the HEX2SMALLINT functionality, another option is to have your data flow move the data to a Data Station operator ( define the operator using a generated temp table ) storing the data as a VARCHAR FOR BIT DATA on LUW temporarily and then apply the HEX2SMALLINT in a Select List operator from the Data Station.

    It doesn't sound like the format of your data is always a valid hex string. If the data is not always a valid hex string, then you would likely need to split the incoming data into multiple target columns - unless your final goal is to get back to a VARCHAR with a base 10 representation for numbers and string values for any invalid number.

    In that type of situation you probably would want to take the HEX2SMALLINT function that Serge talks about and make some modifications to return a string ( so it becomes HEX2STRINGNUM or something like that ). Obviously you would need to handle the invalid character case differently to maybe return the original value ( or null or some other value that you decide ) instead of throwing the conversion error.

    Hopefully something here is useful - if you want to post a data flow and some sample data to clarify things I'd be happy to take a look and see if any other ideas come to mind.

    Best regards,
    JP