Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

Help on Hex in Db2 query

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
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    Post subject: Help on Hex in Db2 query
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: 6966
Location: porcelain throne

PostPosted: Thu Apr 26, 2012 6:23 pm    Post subject:
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: 1278
Location: Belgium

PostPosted: Thu Apr 26, 2012 6:35 pm    Post subject:
Reply with quote

http://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    Post subject:
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: 1278
Location: Belgium

PostPosted: Fri Apr 27, 2012 3:31 pm    Post subject:
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    Post subject:
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

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Mon Apr 30, 2012 8:47 pm    Post subject:
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: 1278
Location: Belgium

PostPosted: Wed May 02, 2012 1:47 pm    Post subject:
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    Post subject:
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: 1278
Location: Belgium

PostPosted: Wed May 02, 2012 6:03 pm    Post subject:
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    Post subject:
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: 1278
Location: Belgium

PostPosted: Thu May 03, 2012 1:17 pm    Post subject:
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    Post subject:
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: 1278
Location: Belgium

PostPosted: Thu May 03, 2012 1:56 pm    Post subject:
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    Post subject:
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: 1218
Location: Israel

PostPosted: Thu May 03, 2012 7:15 pm    Post subject:
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    Post subject:
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    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
This topic is locked: you cannot edit posts or make replies. Db2 SQL query to convert rows into co... subratarec DB2 5 Thu Nov 17, 2016 4:51 pm
No new posts Two Selects in a single select query Rohit Umarjikar DB2 1 Fri Nov 04, 2016 8:46 pm
No new posts Problem in Running Query via JCL vickey_dw DB2 3 Tue Oct 18, 2016 11:11 pm
No new posts SQL Query optimization. arunsoods DB2 12 Sun Sep 04, 2016 6:27 am
No new posts Query on BLSR Nileshkul JCL & VSAM 2 Sat Aug 13, 2016 5:18 am


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us