IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

Help on Hex in Db2 query
IBM Mainframe Forums -> DB2
Post new topic   Reply to topic Quick References
View previous topic :: View next topic  
Author Message
gylbharat

Active Member


Joined: 31 Jul 2009
Posts: 565
Location: Bangalore
PostPosted: Thu Apr 26, 2012 4:56 pm
 Reply with quote
I want to know if we can generate the Hex condition in the where clause at runtime?

Example:
The below sql query works. Output 10 Rows.

COL1 is a CHAR(10) column, it stores the LOG RBA in HEX format.

Code:

SELECT *
FROM TABLE where COL1 > X'00000000000000000000000000'
;


I want to do something like this: - The below doesnot work as the sub select returns a string value and Not HEX value. Hence the output fetched 0 rows.

Code:

SELECT *
FROM TABLE where COL1 > (SELECT 'X'''||substr('2012-04-16-15.43.13.00000000000000000000000000',27)||'''' FROM SYSIBM.SYSDUMMY1)
;


Please let me know how this can be achieved in the singe SQL query?

Thanks in Advance

Gylbharat
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6965
Location: porcelain throne
PostPosted: Thu Apr 26, 2012 6:23 pm
 Reply with quote
why not simply tell us what you want to use in the compare against col1?

this:
'X'''||substr('2012-04-16-15.43.13.00000000000000000000000000',27)||''''
as you have found out resolves to nothing that can be used.

the - and . in your substr will not resolve as acceptable hex values in a hex string.

select hex(col1) from table and show us the output.
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1281
Location: Belgium
PostPosted: Thu Apr 26, 2012 6:35 pm
 Reply with quote
blog.badvised.com/2009/12/converting-rba-to-timestamp.html
Back to top
View user's profile Send private message
gylbharat

Active Member


Joined: 31 Jul 2009
Posts: 565
Location: Bangalore
PostPosted: Fri Apr 27, 2012 12:43 pm
 Reply with quote
Hi,

We have a table where IBMSNAP_INTENTSEQ is present. This column is populated by DPROP setup. It contains the Hex string in unreadable form. IBMSNAP_INTENTSEQ is CHAR(10) [Not Readable]. When we use HEX function to get the value, It expands to CHAR(20) [Readable format]

As shown below.

Code:

---------+---------+---------+---------+---------+---------+----
SELECT IBMSNAP_INTENTSEQ,HEX(IBMSNAP_INTENTSEQ) AS HEXIBM FROM 
TABLE01                                       
WITH UR;                                                       
---------+---------+---------+---------+---------+---------+----
IBMSNAP_INTENTSEQ  HEXIBM                                       
---------+---------+---------+---------+---------+---------+----
..I_vpE@..         0000C96DA597C57C0001                         
..I_w"ì£..         0000C96DA67F58B10001                         
..I_w1.ê..         0000C96DA6F132520001                         
..I_xÁĦ..         0000C96DA765636A0001                         
..I_¥*.#..         0000C96DB25C3B7B0001                         
..I_[.W...         0000C96DBA07E62E0001                         
..I_[%AT..         0000C96DBA6CC1E30001                         
..I_[Lõm..         0000C96DBAD3CF940001                         
..I_².HØ..         0000C96DEA37C8800001                         


We store this hexibm value in a variable along with the concatenated timestamp as shown below:

Code:
2012-03-31-10.10.10.0000000000C96DA597C57C0001


Now we want to read the data from the table dynamically by substr('2012-03-31-10.10.10.0000000000C96DA597C57C0001',27) --> This will extract only the hex number part as starting position is 27 in substr.

If I try the below query
Code:
SELECT * FROM 
TABLE01           
where HEX(IBMSNAP_INTENTSEQ) > '00000000000000000000'                             
WITH UR;         


This will also not yield the correct results as Now hex is getting compared with CHAR.

In HEX, Sequence is -- 0,1,2,3,4....A,B,C,D,E,F

In CHAR (EBCDIC) Sequence is -- A,B,C,D,E,F....Z,0,1,2,3,....9

This is why I want to make the query dynamic as shown in the above post.

Not sure how this can be achieved in single query.

Thanks in Advance.
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1281
Location: Belgium
PostPosted: Fri Apr 27, 2012 3:31 pm
 Reply with quote
Here is a UserDefinedFunction which is the opposite from HEX()
just make sure that length(input string) is divisible by 2. ie. Hex characters are paired.
Code:
CREATE FUNCTION
 My_HEX2EBCDIC (I_STRING VARCHAR(50))
 RETURNS VARCHAR(50)
 
BEGIN
DECLARE v_hex1 char(1);
DECLARE v_hex2 char(1);
DECLARE v_int INTEGER;
DECLARE v_start INTEGER;
DECLARE v_string varchar(50);

SET v_start = 1;
SET v_string = '';

WHILE v_start < length(I_string) DO
  SET v_hex1 = substr(i_string,v_start,1);
  SET v_hex2 = substr(i_string,v_start+1,1);
  SET v_int = (locate(v_hex1,'0123456789ABCDEF') - 1 ) * 16 + locate(v_hex2,'0123456789ABCDEF') - 1 ;
  set v_string = v_string || EBCDIC_CHR(v_int);
  SET v_Start = v_Start + 2;
END WHILE;
return v_String;
END;


Code:
select My_hex2ebcdic('F0F1F2')
, My_hex2ebcdic(HEX('GuyC'))  from sysibm.sysdummy1
Back to top
View user's profile Send private message
gylbharat

Active Member


Joined: 31 Jul 2009
Posts: 565
Location: Bangalore
PostPosted: Mon Apr 30, 2012 12:04 pm
 Reply with quote
Thanks GuyC... Not sure if DBA's will agree to have a UDF created in our shop...
Back to top
View user's profile Send private message
dick scherrer

Moderator Emeritus


Joined: 23 Nov 2006
Posts: 19243
Location: Inside the Matrix
PostPosted: Mon Apr 30, 2012 8:47 pm
 Reply with quote
Hello,

Quote:
Not sure if DBA's will agree to have a UDF created in our shop...
If they do not agree, maybe they should be responsible for providing a solution. . .

If this is truly needed and they don't have a way to do this and will not agree on your "own code" (GuyC's), it will be time to talk with your manager to learn how to proceed. . .

Just my $.02. . .
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1281
Location: Belgium
PostPosted: Wed May 02, 2012 1:47 pm
 Reply with quote
Code:
with cte1 (HEXIBM) as (
select hex(substr('2012-04-16-15.43.13.0000000000000000',1,27)) from sysibm.sysdummy1
)

, cte2 (pstart,istring,ostring) as (
Select 3,hexibm ,
      varchar(ebcdic_chr((locate(substr(hexibm,1,1),'0123456789ABCDEF') - 1 ) * 16
     + locate(substr(hexibm,2,1),'0123456789ABCDEF') - 1 ),50)
from cte1
union all
Select pstart+2,istring ,
      ostring || ebcdic_chr((locate(substr(istring,pstart,1),'0123456789ABCDEF') - 1 ) * 16
     + locate(substr(istring,pstart+1,1),'0123456789ABCDEF') - 1 )
from cte2
where pstart < length(istring)
)

, cte3 (ostring) as (
select ostring from cte2
where pstart > length(istring)
)
--select * from cte3

SELECT IBMSNAP_INTENTSEQ,HEX(IBMSNAP_INTENTSEQ) AS HEXIBM FROM 
TABLE01, cte3 where
table01.IBMSNAP_INTENTSEQ = cte3.ostring                                       
WITH UR;   

 
Back to top
View user's profile Send private message
gylbharat

Active Member


Joined: 31 Jul 2009
Posts: 565
Location: Bangalore
PostPosted: Wed May 02, 2012 4:57 pm
 Reply with quote
Hi GuyC,

Thanks for the reply... Can you explain the query please.

Thanks in advance.
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1281
Location: Belgium
PostPosted: Wed May 02, 2012 6:03 pm
 Reply with quote
I lost track of the original question when trying to solve this.
This is the modified query which should be close to your requirement
Code:
with cte1 (HEXIBM) as (
select substr('2012-03-31-10.10.10.0000000000C96DA597C57C0001',27) from sysibm.sysdummy1
)

CTE1 will contain a varchar column '0000C96DA597C57C0001'
Code:
, cte2 (pstart,istring,ostring) as (
Select 3,hexibm ,
      varchar(ebcdic_chr((locate(substr(hexibm,1,1),'0123456789ABCDEF') - 1 ) * 16
     + locate(substr(hexibm,2,1),'0123456789ABCDEF') - 1 ),50)
from cte1
union all
Select pstart+2,istring ,
      ostring || ebcdic_chr((locate(substr(istring,pstart,1),'0123456789ABCDEF') - 1 ) * 16
     + locate(substr(istring,pstart+1,1),'0123456789ABCDEF') - 1 )
from cte2
where pstart < length(istring)
)
CTE2 will convert this into EBCDIC varchar using recursive SQL, one byte at the time giving you multiple rows
Code:
, cte3 (ostring) as (
select ostring from cte2
where pstart > length(istring)
)

cte3 will contain only the last row of CTE2 giving you the EBCDIC representation of x'0000C96DA597C57C0001'

then you can join any table with cte3 using this varchar as join-criteria.

Code:
SELECT IBMSNAP_INTENTSEQ,HEX(IBMSNAP_INTENTSEQ) AS HEXIBM FROM 
TABLE01, cte3 where
table01.IBMSNAP_INTENTSEQ = cte3.ostring                                       
WITH UR;   


Sorry for the confusion .
Back to top
View user's profile Send private message
gylbharat

Active Member


Joined: 31 Jul 2009
Posts: 565
Location: Bangalore
PostPosted: Thu May 03, 2012 12:10 pm
 Reply with quote
Thanks for the explanation GuyC... But unfortunately I am not able to run this query as getting an error SQLCODE -4700.

EBCDIC_CHR function is available in V9. We are still in V9 Compatibility mode. This function will be available in NFM.
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1281
Location: Belgium
PostPosted: Thu May 03, 2012 1:17 pm
 Reply with quote
Then you'll have to do it the old fashioned way : in your program.
Back to top
View user's profile Send private message
gylbharat

Active Member


Joined: 31 Jul 2009
Posts: 565
Location: Bangalore
PostPosted: Thu May 03, 2012 1:31 pm
 Reply with quote
Hmm... Thanks Anyways icon_smile.gif
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1281
Location: Belgium
PostPosted: Thu May 03, 2012 1:56 pm
 Reply with quote
it's a shitty solution, but it works :
Code:
with cte0 (char1,char2) as (
select '0',x'000102030405060708090A0B0C0D0E0F' from sysibm.sysdummy1 union all
select '1',x'101112131415161718191A1B1C1D1E1F' from sysibm.sysdummy1 union all
select '2',x'202122232425262728292A2B2C2D2E2F' from sysibm.sysdummy1 union all
select '3',x'303132333435363738393A3B3C3D3E3F' from sysibm.sysdummy1 union all
select '4',x'404142434445464748494A4B4C4D4E4F' from sysibm.sysdummy1 union all
select '5',x'505152535455565758595A5B5C5D5E5F' from sysibm.sysdummy1 union all
select '6',x'606162636465666768696A6B6C6D6E6F' from sysibm.sysdummy1 union all
select '7',x'707172737475767778797A7B7C7D7E7F' from sysibm.sysdummy1 union all
select '8',x'808182838485868788898A8B8C8D8E8F' from sysibm.sysdummy1 union all
select '9',x'909192939495969798999A9B9C9D9E9F' from sysibm.sysdummy1 union all
select 'A',x'A0A1A2A3A4A5A6A7A8A9AAABACADAEAF' from sysibm.sysdummy1 union all
select 'B',x'B0B1B2B3B4B5B6B7B8B9BABBBCBDBEBF' from sysibm.sysdummy1 union all
select 'C',x'C0C1C2C3C4C5C6C7C8C9CACBCCCDCECF' from sysibm.sysdummy1 union all
select 'D',x'D0C1C2C3C4C5C6C7C8C9CACBCCCDCECF' from sysibm.sysdummy1 union all
select 'E',x'E0E1E2E3E4E5E6E7E8E9EAEBECEDEEEF' from sysibm.sysdummy1 union all
select 'F',x'F0F1F2F3F4F5F6F7F8F9FAFBFCFDFEFF' from sysibm.sysdummy1
)

, cte1 (HEXIBM) as (
select substr('2012-03-31-10.10.10.0000000000C96DA597C57C0001',27) from sysibm.sysdummy1
) 

, cte2 (pstart,istring,ostring) as (
Select 3,hexibm , 
      varchar(substr(cte0.char2, locate(substr(hexibm,2,1),'0123456789ABCDEF') ,1),50)
from cte1, cte0 where cte0.char1 = substr(hexibm,1,1)   
union all
Select pstart + 2,istring ,
      ostring || varchar(substr(cte0.char2,locate(substr(istring,pstart + 1,1),'0123456789ABCDEF') ,1),50)
from cte2 ,cte0
where  pstart < length(istring)
and cte0.char1 = substr(istring,pstart,1)

) 
, cte3 (ostring) as (
select ostring from cte2
where pstart > length(istring)
)

SELECT IBMSNAP_INTENTSEQ,HEX(IBMSNAP_INTENTSEQ) AS HEXIBM FROM 
TABLE01, cte3 where
table01.IBMSNAP_INTENTSEQ = cte3.ostring                                       
WITH UR;   
Back to top
View user's profile Send private message
gylbharat

Active Member


Joined: 31 Jul 2009
Posts: 565
Location: Bangalore
PostPosted: Thu May 03, 2012 3:40 pm
 Reply with quote
Thanks GuyC... It works very well... icon_smile.gif
Back to top
View user's profile Send private message
Marso

REXX Moderator


Joined: 13 Mar 2006
Posts: 1356
Location: Israel
PostPosted: Thu May 03, 2012 7:15 pm
 Reply with quote
I still haven't understood why it is necessary to prepend a timestamp just to have it removed.

What is the difference between:
Code:
select substr('2012-03-31-10.10.10.0000000000C96DA597C57C0001',27) from sysibm.sysdummy1
and
Code:
select '0000C96DA597C57C0001' from sysibm.sysdummy1
?
Back to top
View user's profile Send private message
gylbharat

Active Member


Joined: 31 Jul 2009
Posts: 565
Location: Bangalore
PostPosted: Mon May 07, 2012 11:31 am
 Reply with quote
There is a logic where we store the current timestamp concatenated with LLRN number. This helps in orderby in EBCDIC.
Back to top
View user's profile Send private message
View previous topic : : View next topic  
Post new topic   Reply to topic All times are GMT + 6 Hours
Forum Index -> DB2

 

Similar Topics
Topic Forum Replies
No new posts DB2 Query to get the count of Unique ... DB2 2
No new posts Need help with ADABAS query (COBOL-AD... All Other Mainframe Topics 1
No new posts RC query -Time column CA Products 3
No new posts Dynamically pass table name to a sele... DB2 2
No new posts Query on edit primary command CLIST & REXX 5
Search our Forums:


Back to Top
IBMMainframes.com is not an official and/or affiliated with IBM® in anyway
Board Rules | FAQ | Downloads | Wiki | SiteMap | Contact Us