Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

SQL Server equivalent to DB2 HEX Expand / Collapse
Author
Message
Posted Wednesday, January 11, 2012 10:40 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Wednesday, August 27, 2014 1:06 PM
Points: 1,563, Visits: 2,392
Greetings all. I'm using SQL Server 2008 R2 Express, and I've got a linked server to a DB2 mainframe. There is one particular field where I must use the DB2 HEX function to return a varchar value. I have been using OPENQUERY, so have been able to pull out what I need with the DB2 HEX function. I'll give one example to illustrate. If I do this...
SELECT
*
FROM OPENQUERY(LinkedServer,'
SELECT
ID,
Column1,
LENGTH(Column1),
HEX(Column1) AS HEX_VAL
FROM Schema.Table
WHERE ID = 1
WITH UR;
')

My results look like this

ID	Column1	HEX_VAL	                                     LENGTH
1 B C200000012700CD400000003673CD500000000850C 42




Now, I know that the HEX_VAL string is simply a string of 14 character pieces (I'll call them "words"), from which I can extract a value by pulling out substrings. For example, CAST(SUBSTRING(HEX_VAL,3,11) AS DECIMAL(11,2))/100.0 = 127.00. No matter how many "words" are in the string, I can use a Tally table to reliably pull out what I need. My problem is that I have figured out how to configure my linked server so that I can use 4 part naming convention instead of OPENQUERY. This is great, however, there is no SQL Server equivalent to what the DB2 HEX function is doing. The closest I can muster is to convert to varbinary, which looks like this...

SELECT TOP 1
ID,
Column1,
VARBIN = CAST(Column1 AS VARBINARY),
LENGTH = LEN(Column1)
FROM LinkedServer..Schema.Table
WHERE ID = 1

with results...
ID	Column1	VARBIN	                                       LENGTH
1 B 0x4200000012F80C4D00000003C5144E00000000650C 42





So, I guess I have a couple of questions.
1) Why does the value of the field return 'B' when no conversion is happening, even though SQL Server knows the len is 42?
2) What is the difference between ...
C200000012700CD400000003673CD500000000850C and 0x4200000012F80C4D00000003C5144E00000000650C?
3) Is there a way to turn the second string into the first string?
4) Knowing that I can pull out all the values I need from the HEX string by doing...
DECLARE @string VARCHAR(100)
SELECT @string = 'C200000012700CD400000003673CD500000000850C'

SELECT
SUBSTRING(@string,CASE WHEN N = 3 THEN N ELSE N + 3 END,11)
FROM tally t
WHERE (t.n = 3
OR t.N%14 = 0)
AND t.N < LEN(@string)

-----------------------------------
results
00000012700
00000003673
00000000850


... is there a way to pull out the same values from the VARBIN string? Other than the length of the two strings, I don't see any reliable patterns. I sure would appreciate any insight into any aspect of my dilema, and, as always, I very much appreciate the time you all take to consider other's problems.



Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
Post #1234192
Posted Wednesday, January 11, 2012 11:52 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: 2 days ago @ 10:07 PM
Points: 9,926, Visits: 11,183
DB2 HEX appears to do a similar thing as using the optional style parameter of CONVERT when converting from binary to a string type:

SELECT 
CONVERT(char(4), 0xDEADBEEF), -- 'Þ­¾ï'
CONVERT(char(10), 0xDEADBEEF, 1), -- '0xDEADBEEF'
CONVERT(char(8), 0xDEADBEEF, 2) -- 'DEADBEEF'

Q1: Does the Column1 value really have length 42? (e.g. trailing non-spaces).
Q2: Hope this is now irrelevant. Possibly the DB2 binary storage format for the column.
Q3: Possibly. Would need to see the query plan and know the data types involved.
Q4: No answer needed now, I hope.




Paul White
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #1234257
Posted Wednesday, January 11, 2012 12:38 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Wednesday, August 27, 2014 1:06 PM
Points: 1,563, Visits: 2,392
Thank you Paul. I'm still a bit confused, but I did find where the 'B' is coming from, sort of...


DECLARE @string VARCHAR(100)
SELECT @string = 0x4200000012F80C4D00000003C5144E00000000650C

SELECT CONVERT(CHAR(10),@string)


The 'B' shows up no matter what I put in for the length parameter of CHAR. I still don't know what it means.


Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
Post #1234299
Posted Wednesday, January 11, 2012 12:57 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: 2 days ago @ 10:07 PM
Points: 9,926, Visits: 11,183
Greg Snidow (1/11/2012)
The 'B' shows up no matter what I put in for the length parameter of CHAR. I still don't know what it means.

So 'B' is just ASCII character 66 = 0x42. The second byte of the binary is zero, and ASCII(0) is interpreted by SQL Server Management Studio as end-of-string when displaying grid view results. Thank you Drew.




Paul White
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #1234315
Posted Wednesday, January 11, 2012 1:44 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, September 3, 2013 8:24 AM
Points: 1,240, Visits: 5,421
SQL Kiwi (1/11/2012)
Greg Snidow (1/11/2012)
The 'B' shows up no matter what I put in for the length parameter of CHAR. I still don't know what it means.

So 'B' is just ASCII character 66 = 0x42. The second byte of the binary is zero, and ASCII(0) is interpreted by SQL Server as end-of-string.


ASCII(0) is interpreted as end-of-string by some software, but SQL Server is most definitely NOT one of them, although it appears that the grid view in SSMS is. If you calculate the Len() of your string it will return 10 (not 1) or if you switch your query to output "Results to Text" or if you use the FOR XML clause to produce xml results you will see that SQL Server is returning the full string.

Drew


J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
Post #1234347
Posted Wednesday, January 11, 2012 1:59 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: 2 days ago @ 10:07 PM
Points: 9,926, Visits: 11,183
drew.allen (1/11/2012)
ASCII(0) is interpreted as end-of-string by some software, but SQL Server is most definitely NOT one of them, although it appears that the grid view in SSMS is.

Yes, well done.




Paul White
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #1234361
Posted Wednesday, January 11, 2012 2:21 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Wednesday, August 27, 2014 1:06 PM
Points: 1,563, Visits: 2,392
Thank you both for the tips. After doing some research (google), I believe I am working with a packed decimal field. I suspected this was the case when I FTP'd a text extract, and the data looked like a bunch of non-language characters. I found some stuff on EBCDIC, and this looks like what I have. Any ideas how to deal with this? I saw a post from 2007 here about converting it when imported from a text file, but nothing about how to deal with it when getting it VIA linked server.

Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
Post #1234385
Posted Wednesday, January 11, 2012 2:41 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: 2 days ago @ 10:07 PM
Points: 9,926, Visits: 11,183
Greg Snidow (1/11/2012)
Thank you both for the tips. After doing some research (google), I believe I am working with a packed decimal field. I suspected this was the case when I FTP'd a text extract, and the data looked like a bunch of non-language characters. I found some stuff on EBCDIC, and this looks like what I have. Any ideas how to deal with this? I saw a post from 2007 here about converting it when imported from a text file, but nothing about how to deal with it when getting it VIA linked server.

Just out of interest, what are you using to connect to DB2? I only ask because, although I have never used a DB2 linked server myself, I have used many other types, and it really ought not to be this hard. There's a bunch of stuff out there about connecting to DB2, this is just one promising link I found on a quick search: http://blogs.msdn.com/b/dotnetinterop/archive/2006/01/20/defining-a-db2-as-a-linked-server.aspx




Paul White
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #1234398
Posted Wednesday, January 11, 2012 3:46 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Wednesday, August 27, 2014 1:06 PM
Points: 1,563, Visits: 2,392
SQL Kiwi (1/11/2012)
Just out of interest, what are you using to connect to DB2?


Paul
I'm using the MSDASQL provider. I tried using the IBM provider that came with my IBM software, but I cannot get it to work with the 4 part naming convention, even with 'Level zero only' unchecked. I could only get data by using OPENQUERY, which is fine, but none of the OLAP functions work through ODBC connections to our mainframe. I don't know if there is a permissions issue there, but I need to use them. I found that by using the MS provider, I can use 4 part naming, and have the full power of SQL Server, which is my preference. I am 99% happy with it now, except for those darned packed decimal fields. A friend of mine, who is connecting to the same system with SAS, told me he has the same issue with the packed decimals. I am wondering if there is a configuration option I can set. Anyhow, I totally agree with you, this should not be this difficult. Thank you.


Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
Post #1234436
Posted Thursday, January 12, 2012 5:04 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Wednesday, August 27, 2014 1:06 PM
Points: 1,563, Visits: 2,392
Well my hopes are all but dashed now. I spoke to our mainframe guru, and she told me the column is a user defined data type. The first two bytes contain the length of the field in binary format, then the remaining bytes are an intermingled mix of signed packed decimal and alphanumeric data. I just don't think I can spar against that kind of data engineering. One last grasp at the proverbial straws though: is it possible to read the column bit by bit?

Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
Post #1235317
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse