InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSun, 19 May 2013 23:27:48 GMT20http://www.sqlservercentral.com/Forums/Topic1234192-392-1.aspx[quote][b]Peter Brinkhaus (1/14/2012)[/b][hr]Besides, the SELECT @x = ...@x ... method seems to be unreliable (sorry, don't have details at hand, but maybe Paul is willing to jump in).[/quote]It was only supported for backward compatibility, and only where an explicit ORDER BY clause is provided. That said, the compatibility is very limited and many of us have seen trivial examples where this best-effort promise for backward compatibility is broken. I definitely do not recommend it. More information on the ordering guarantee situation as it was back in 2005...[url]http://blogs.msdn.com/b/sqltips/archive/2005/07/20/441053.aspx[/url][quote]Here's a quick test[/quote]The optimizer is smart enough to realise you are translating the same value over and over again. It will save the result from the first execution and just replay that for every subsequent row that has the same input value. This does depend on the function being deterministic, so I can't say whether this actually happened in your case, but it's something to be aware of.I had a quick look at the scalar function. This in-line version produces correct results for me, and might be even faster than the rCTE version:[code="sql"]ALTER FUNCTION dbo.AsciiToEbcdic( @v VARBINARY(8000))RETURNS TABLEWITH SCHEMABINDING ASRETURN SELECT CONVERT(varbinary(8000), q.result, 2) AS result FROM ( SELECT CONVERT(char(2), ae.EBCDIC_VAL, 2) FROM dbo.Tally AS t JOIN dbo.ASCII_EBCDIC AS ae ON SUBSTRING(@v, t.N, 1) = ae.ASCII_VAL WHERE t.N <= DATALENGTH(@v) ORDER BY t.N FOR XML PATH ('') ) AS q (result)[/code][code="sql"]SELECT ate.resultFROM dbo.AsciiToEbcdic (0x4200000012F80C4D00000003C5144E00000000650C) AS ate[/code]Sat, 14 Jan 2012 13:54:32 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic1234192-392-1.aspx[quote][b]Greg Snidow (1/13/2012)[/b][hr]Peter, I looked at the link you provided for the lookup table, and decided to create a permanent table to use in the function.[/quote]That's a good thing to do.[quote]I also tweaked it to return only the final value, and to use a tally table instead of a recursive CTE. I'm not sure which is better, but I have never used a recursive CTE, so I wanted to stick with a solution I fully understood.[/quote]It's a perfectly valid argument to stick with a solution you fully understand, especially if you pick something up from the internet. However, in this case I would stay with the recursive CTE just because it performs better. Scalar functions do not perform very well. Besides, the SELECT @x = ...@x ... method seems to be unreliable (sorry, don't have details at hand, but maybe Paul is willing to jump in). Here's a quick test:[code="sql"]DECLARE @V VARBINARY(100) = 0x4200000012F80C4D00000003C5144E00000000650CSET STATISTICS TIME ONPRINT '-- Scalar function'select top 10000 dbo.fnAsciiToEbcdic(@V) Vfrom sys.columns C1cross join sys.columns C2PRINT '-- Recursive CTE'SELECT TOP 10000 A2E.*FROM sys.columns C1CROSS JOIN sys.columns C2CROSS APPLY dbo.AsciiToEbcdic(@V) A2ESET STATISTICS TIME OFF[/code] Result:[code="plain"]-- Scalar function(20000 row(s) affected) SQL Server Execution Times: CPU time = 1466 ms, elapsed time = 1545 ms.-- Recursive CTE(20000 row(s) affected) SQL Server Execution Times: CPU time = 16 ms, elapsed time = 252 ms.[/code]Anyway, thanks for the very positive feedback.PeterSat, 14 Jan 2012 13:05:56 GMTPeter Brinkhaushttp://www.sqlservercentral.com/Forums/Topic1234192-392-1.aspxPaul, thank you very much. I'm really honoured by such a compliment from the master of T-SQL & query optimization. However, I still feel a little bit uncomfortable about the solution I suggested. It would have been nice if someone could have pointed Greg in the right direction to properly configure the linked server, if at all possible. I found out that DB2 knows something like a structered type (comparable to a struct in any C-like language) and I'm afraid the provider does not support that feature very well. Any way, I see the solution I provided as some kind of a last resort. It's slow and it might turn into a real maintenance nightmare.Thanks again, PeterSat, 14 Jan 2012 12:43:36 GMTPeter Brinkhaushttp://www.sqlservercentral.com/Forums/Topic1234192-392-1.aspx[quote][b]Peter Brinkhaus (1/13/2012)[/b][hr]What's the data type of Column1 one in DB2? It looks like a structure containing 6 columns...[/quote]I have to say Peter, this is one of the best posts I have ever seen on SSC. Above and beyond the call of duty! :-)Fri, 13 Jan 2012 19:46:18 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic1234192-392-1.aspxPeter, I looked at the link you provided for the lookup table, and decided to create a permanent table to use in the function. I also tweaked it to return only the final value, and to use a tally table instead of a recursive CTE. I'm not sure which is better, but I have never used a recursive CTE, so I wanted to stick with a solution I fully understood. A funny thing I noticed is that my function does not work without an index on the ascii values of the lookup table. Anyhow, I'll go ahead and post the code to create the look up table and the function (which assumes the existence of a tally table starting at 1) in case anyone is interested. Peter, once again, thank you for your so valuable post and code. I learned a great deal in tearing it apart until I fully understood what it is doing. Take care.First the function...[code]USE masterGOIF OBJECT_ID('dbo.fnAsciiToEbcdic','fn') IS NOT NULL DROP FUNCTION dbo.fnAsciiToEbcdicGOCREATE FUNCTION [dbo].[fnAsciiToEbcdic](@v VARBINARY(8000))RETURNS VARBINARY(8000)AS BEGIN DECLARE @return VARBINARY(8000) SELECT @return = ISNULL(@return,CAST('' AS VARBINARY(8000))) + a2e.EBCDIC_VAL FROM master..Tally t INNER JOIN master..ASCII_EBCDIC a2e ON SUBSTRING(@v,t.N,1) = a2e.ASCII_VAL WHERE N <= DATALENGTH(@v) RETURN @returnEND[/code]And now the lookup table...[code]USE masterGOIF OBJECT_ID('master..ASCII_EBCDIC','u') IS NOT NULL DROP TABLE master..ASCII_EBCDICGOCREATE TABLE master..ASCII_EBCDIC( ID INT IDENTITY(1,1), ASCII_VAL BINARY, EBCDIC_VAL BINARY)INSERT INTO master..ASCII_EBCDICVALUES(0x00, 0x00),(0x01, 0x01),(0x02, 0x02),(0x03, 0x03),(0x04, 0x37),(0x05, 0x2D),(0x06, 0x2E),(0x07, 0x2F),(0x08, 0x16),(0x09, 0x05),(0x0A, 0x25),(0x0B, 0x0B),(0x0C, 0x0C),(0x0D, 0x0D),(0x0E, 0x0E),(0x0F, 0x0F),(0x10, 0x10),(0x11, 0x11),(0x12, 0x12),(0x13, 0x13),(0x14, 0x3C),(0x15, 0x3D),(0x16, 0x32),(0x17, 0x26),(0x18, 0x18),(0x19, 0x19),(0x1A, 0x3F),(0x1B, 0x27),(0x1C, 0x1C),(0x1D, 0x1D),(0x1E, 0x1E),(0x1F, 0x1F),(0x20, 0x40),(0x21, 0x5A),(0x22, 0x7F),(0x23, 0x7B),(0x24, 0x5B),(0x25, 0x6C),(0x26, 0x50),(0x27, 0x7D),(0x28, 0x4D),(0x29, 0x5D),(0x2A, 0x5C),(0x2B, 0x4E),(0x2C, 0x6B),(0x2D, 0x60),(0x2E, 0x4B),(0x2F, 0x61),(0x30, 0xF0),(0x31, 0xF1),(0x32, 0xF2),(0x33, 0xF3),(0x34, 0xF4),(0x35, 0xF5),(0x36, 0xF6),(0x37, 0xF7),(0x38, 0xF8),(0x39, 0xF9),(0x3A, 0x7A),(0x3B, 0x5E),(0x3C, 0x4C),(0x3D, 0x7E),(0x3E, 0x6E),(0x3F, 0x6F),(0x40, 0x7C),(0x41, 0xC1),(0x42, 0xC2),(0x43, 0xC3),(0x44, 0xC4),(0x45, 0xC5),(0x46, 0xC6),(0x47, 0xC7),(0x48, 0xC8),(0x49, 0xC9),(0x4A, 0xD1),(0x4B, 0xD2),(0x4C, 0xD3),(0x4D, 0xD4),(0x4E, 0xD5),(0x4F, 0xD6),(0x50, 0xD7),(0x51, 0xD8),(0x52, 0xD9),(0x53, 0xE2),(0x54, 0xE3),(0x55, 0xE4),(0x56, 0xE5),(0x57, 0xE6),(0x58, 0xE7),(0x59, 0xE8),(0x5A, 0xE9),(0x5B, 0xBA),(0x5C, 0xE0),(0x5D, 0xBB),(0x5E, 0xB0),(0x5F, 0x6D),(0x60, 0x79),(0x61, 0x81),(0x62, 0x82),(0x63, 0x83),(0x64, 0x84),(0x65, 0x85),(0x66, 0x86),(0x67, 0x87),(0x68, 0x88),(0x69, 0x89),(0x6A, 0x91),(0x6B, 0x92),(0x6C, 0x93),(0x6D, 0x94),(0x6E, 0x95),(0x6F, 0x96),(0x70, 0x97),(0x71, 0x98),(0x72, 0x99),(0x73, 0xA2),(0x74, 0xA3),(0x75, 0xA4),(0x76, 0xA5),(0x77, 0xA6),(0x78, 0xA7),(0x79, 0xA8),(0x7A, 0xA9),(0x7B, 0xC0),(0x7C, 0x4F),(0x7D, 0xD0),(0x7E, 0xA1),(0x7F, 0x07),(0x80, 0x20),(0x81, 0x21),(0x82, 0x22),(0x83, 0x23),(0x84, 0x24),(0x85, 0x15),(0x86, 0x06),(0x87, 0x17),(0x88, 0x28),(0x89, 0x29),(0x8A, 0x2A),(0x8B, 0x2B),(0x8C, 0x2C),(0x8D, 0x09),(0x8E, 0x0A),(0x8F, 0x1B),(0x90, 0x30),(0x91, 0x31),(0x92, 0x1A),(0x93, 0x33),(0x94, 0x34),(0x95, 0x35),(0x96, 0x36),(0x97, 0x08),(0x98, 0x38),(0x99, 0x39),(0x9A, 0x3A),(0x9B, 0x3B),(0x9C, 0x04),(0x9D, 0x14),(0x9E, 0x3E),(0x9F, 0xFF),(0xA0, 0x41),(0xA1, 0xAA),(0xA2, 0x4A),(0xA3, 0xB1),(0xA4, 0x9F),(0xA5, 0xB2),(0xA6, 0x6A),(0xA7, 0xB5),(0xA8, 0xBD),(0xA9, 0xB4),(0xAA, 0x9A),(0xAB, 0x8A),(0xAC, 0x5F),(0xAD, 0xCA),(0xAE, 0xAF),(0xAF, 0xBC),(0xB0, 0x90),(0xB1, 0x8F),(0xB2, 0xEA),(0xB3, 0xFA),(0xB4, 0xBE),(0xB5, 0xA0),(0xB6, 0xB6),(0xB7, 0xB3),(0xB8, 0x9D),(0xB9, 0xDA),(0xBA, 0x9B),(0xBB, 0x8B),(0xBC, 0xB7),(0xBD, 0xB8),(0xBE, 0xB9),(0xBF, 0xAB),(0xC0, 0x64),(0xC1, 0x65),(0xC2, 0x62),(0xC3, 0x66),(0xC4, 0x63),(0xC5, 0x67),(0xC6, 0x9E),(0xC7, 0x68),(0xC8, 0x74),(0xC9, 0x71),(0xCA, 0x72),(0xCB, 0x73),(0xCC, 0x78),(0xCD, 0x75),(0xCE, 0x76),(0xCF, 0x77),(0xD0, 0xAC),(0xD1, 0x69),(0xD2, 0xED),(0xD3, 0xEE),(0xD4, 0xEB),(0xD5, 0xEF),(0xD6, 0xEC),(0xD7, 0xBF),(0xD8, 0x80),(0xD9, 0xFD),(0xDA, 0xFE),(0xDB, 0xFB),(0xDC, 0xFC),(0xDD, 0xAD),(0xDE, 0xAE),(0xDF, 0x59),(0xE0, 0x44),(0xE1, 0x45),(0xE2, 0x42),(0xE3, 0x46),(0xE4, 0x43),(0xE5, 0x47),(0xE6, 0x9C),(0xE7, 0x48),(0xE8, 0x54),(0xE9, 0x51),(0xEA, 0x52),(0xEB, 0x53),(0xEC, 0x58),(0xED, 0x55),(0xEE, 0x56),(0xEF, 0x57),(0xF0, 0x8C),(0xF1, 0x49),(0xF2, 0xCD),(0xF3, 0xCE),(0xF4, 0xCB),(0xF5, 0xCF),(0xF6, 0xCC),(0xF7, 0xE1),(0xF8, 0x70),(0xF9, 0xDD),(0xFA, 0xDE),(0xFB, 0xDB),(0xFC, 0xDC),(0xFD, 0x8D),(0xFE, 0x8E),(0xFF, 0xDF)GOIF EXISTS(SELECT * FROM SYSINDEXES WHERE name = 'IX_MASTER_ASCII_EBCDIC_ascii_val') DROP INDEX ASCII_EBCDIC.IX_MASTER_ASCII_EBCDIC_ascii_valGOCREATE UNIQUE CLUSTERED INDEX IX_MASTER_ASCII_EBCDIC_ascii_val ON ASCII_EBCDIC(ascii_val)[/code]Fri, 13 Jan 2012 13:32:20 GMTGreg Snidowhttp://www.sqlservercentral.com/Forums/Topic1234192-392-1.aspxNot even the least bit disappointed, indeed just the opposite is true, as not being able to see the tables is a minor issue. I built a full table of values, and it worked like a charm. My task now is to figure out how to make it so that I can use it in a select list on multiple records. Right now I am unable to do this. Thanks again for your help Peter.Fri, 13 Jan 2012 10:54:03 GMTGreg Snidowhttp://www.sqlservercentral.com/Forums/Topic1234192-392-1.aspxI'm sorry I have to disappoint you, but I don't know DB2 either, so I can't help you on this.Fri, 13 Jan 2012 10:12:35 GMTPeter Brinkhaushttp://www.sqlservercentral.com/Forums/Topic1234192-392-1.aspxPeter, thank you so much for taking your good time to work on my problem, I am truly humbled by your knowledge. [quote][b]Peter Brinkhaus (1/13/2012)[/b][hr]What's the data type of Column1 one in DB2? It looks like a structure containing 6 columns:Pos 1: CHAR(1) - 'B' = 0xC2 (EBCDIC) = 0x42 (ASCII)Pos 2-7: NUMERIC(11) Packed decimal - 0x00000012700C (0xC = plus sign)Pos 8: CHAR(1) - 'M' = 0xD4 (EBCDIC) = 0x4D (ASCII)Pos 9: NUMERIC(11) Packed decimal - 0x00000003673C (0xC = plus sign)Pos 15: CHAR(1) - 'N' = 0xD5 (EBCDIC) = 0x4E (ASCII)Pos 16-21: NUMERIC(11) Packed decimal - 0x00000000850C (0xC = plus sign)[/quote]Your break down makes perfect sense now, as I could not get how a single field could actually be an array. [quote]When using the four part naming convention the entire structure is translated from EBCDIC to ASCII byte by byte.[/quote]The four part naming convention is causing me some trouble, as I can not get it to work with the IBM provider. If I use the MSDASQL provider, I can use it if I leave level zero only unchecked, and omit the catalog. The problem here is that I cannot see the objects. If I use the IBM provider, I can not get 4 part name to work regardless of the level zero value. There is somewhat of a language barrier between the dba's and me, because they do not know SQL Server, and I don't know DB2, so I don't even get what the catalog is. I scripted out my linked server, and tweeked it a bit for others to easily use, maybe you could Identify what I am doing wrong?[code]--==== Declare the local variables needed to make the connection. You will need to run this script -- every time your mainframe password changes.DECLARE @user VARCHAR(100), @pwd VARCHAR(100), @connection VARCHAR(100), @local VARCHAR(100), @LinkedServer VARCHAR(20), @dsn VARCHAR(20) --==== Set the values of the local variables SELECT @user = '#######' --<<<< change this to your EIDSELECT @pwd = '#######' --<<<< change this to your mainframe passwordSELECT @connection = 'Password=' + @pwd + ';Persist Security Info=True;User ID=' + @userSELECT @local = '#######\' + @user --<<<< change to your domainSELECT @LinkedServer = '#######' --<<<< change to your preferenceSELECT @dsn = '#######' --<<<< change to your dsn--==== Drop the linked server if it already exists. IF EXISTS (SELECT srv.name FROM sys.servers srv WHERE srv.server_id != 0 AND srv.name = @LinkedServer) EXEC master.dbo.sp_dropserver @server= @LinkedServer, @droplogins='droplogins';EXEC master.dbo.sp_addlinkedserver @server = @LinkedServer, @srvproduct = 'DB2', @provider = 'MSDASQL', -- can use 4 part name is catalog omitted, level zero only not checked --@provider = 'IBMDADB2', -- 4 part name does not work, don't know what is catalog @datasrc = @dsn, @provstr = @connection; EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname ='MIRROR', @useself ='False', @locallogin = @local, @rmtuser = @user, @rmtpassword = @pwd;[/code]Again, thank you for your help.Fri, 13 Jan 2012 08:41:11 GMTGreg Snidowhttp://www.sqlservercentral.com/Forums/Topic1234192-392-1.aspxWhat's the data type of Column1 one in DB2? It looks like a structure containing 6 columns:Pos 1: CHAR(1) - 'B' = 0xC2 (EBCDIC) = 0x42 (ASCII)Pos 2-7: NUMERIC(11) Packed decimal - 0x00000012700C (0xC = plus sign)Pos 8: CHAR(1) - 'M' = 0xD4 (EBCDIC) = 0x4D (ASCII)Pos 9: NUMERIC(11) Packed decimal - 0x00000003673C (0xC = plus sign)Pos 15: CHAR(1) - 'N' = 0xD5 (EBCDIC) = 0x4E (ASCII)Pos 16-21: NUMERIC(11) Packed decimal - 0x00000000850C (0xC = plus sign)When using the four part naming convention the entire structure is translated from EBCDIC to ASCII byte by byte. For a translation table take a look at [url]http://en.wikipedia.org/wiki/EBCDIC_37[/url]. Note that the least significant nible of the packed decimal values contains 0xC meaning positive. Other values may be 0xD (negative) or 0xF (unsigned) (see [url]http://www.simotime.com/datapk01.htm[/url]).One way to get the packed decimal values out this structure is to translate it back to EBCDIC, convert it to a hex-string and then extracting the values the way you already did using OPENQUERY. Here's some demo code. Note that the Ascii-Ebcdic table is not complete in function AsciiToEbcdic. I just put in enough codes to translate your sample data:[code="sql"]DECLARE @V VARBINARY(100) = 0x4200000012F80C4D00000003C5144E00000000650CSELECT *FROM dbo.AsciiToEbcdic(@V) A2ECROSS APPLY dbo.GetPackedDecimal(A2E.Value, 2, 6) PD1CROSS APPLY dbo.GetPackedDecimal(A2E.Value, 9, 6) PD2CROSS APPLY dbo.GetPackedDecimal(A2E.Value, 16, 6) PD3OPTION (MAXRECURSION 0)[/code]Output:[code="plain"]0xC200000012700CD400000003673CD500000000850D 12700 3673 850[/code]Functions:[code="sql"]IF OBJECT_ID('dbo.AsciiToEbcdic', 'IF') IS NOT NULL DROP FUNCTION dbo.AsciiToEbcdicGOCREATE FUNCTION dbo.AsciiToEbcdic(@V VARBINARY(8000))RETURNS TABLEAS RETURN WITH rCTE(I, Value) AS( SELECT 0 AS I, CAST('' AS VARBINARY(8000)) AS Value UNION ALL SELECT I + 1, Value + A2E.EbcdicCode FROM rCTE JOIN ( --Ascii - Ebcdic table: for all values look at http://en.wikipedia.org/wiki/EBCDIC_37 VALUES (0x00, 0x00), (0x03, 0x03), (0x0C, 0x0C), (0x0D, 0x0D), (0x0F, 0x0F), (0x12, 0x12), (0x42, 0xC2), (0x4D, 0xD4), (0xC5, 0x67), (0xF8, 0x70), (0x14, 0x3C), (0x4E, 0xD5), (0x65, 0x85) ) A2E (AsciiCode, EbcdicCode) ON SUBSTRING(@V, I + 1, 1) = AsciiCode AND I < DATALENGTH(@V) )SELECT ValueFROM rCTE WHERE I = DATALENGTH(@V);GOIF OBJECT_ID('dbo.GetPackedDecimal', 'IF') IS NOT NULL DROP FUNCTION dbo.GetPackedDecimalGOCREATE FUNCTION dbo.GetPackedDecimal(@V VARBINARY(100), @Pos INT, @Len INT)RETURNS TABLEAS RETURN WITH Cte AS( SELECT SUBSTRING(CONVERT(VARCHAR(100), @V, 2), 2 * @Pos - 1, 2 * @Len) R)SELECT CASE RIGHT(R, 1) WHEN 'D' THEN -- '0xC' = Positive, '0xF' = Unsigned -1 ELSE 1 END * CAST(LEFT(R, DATALENGTH(R) - 1) AS DECIMAL(38)) ValueFROM Cte; GO[/code]Fri, 13 Jan 2012 05:01:31 GMTPeter Brinkhaushttp://www.sqlservercentral.com/Forums/Topic1234192-392-1.aspxOk, so I am going to go ahead with the straw grabbing. I was messing around with Drew's suggestion to ouput to text, combined with casting various substrings as various data types, and I ended up with this...and I don't even know it it will display here...[code]------------------------------øMøMd<CßæC… M[/code]Indeed, they are not displaying as they do in the output. However, I took one of them at random, this guy, '', and converted to ASCII...[code]SELECT ASCII('')-----------8(1 row(s) affected)[/code]So, if ASCII 8 = backspace, does anyone think this has any meaning, or is SQL Server just totally confused by the nature of this data? In my results pane, the character actually looks like a vertically aligned rectangle with a slightly ovoid white dot in the center. Could this be the seemingly confounding EBCDIC? At this point, I think I'm going to stick with OPENQUERY, so any further discussion would be academic. That is, of course, unless anyone thinks my errand is anything other than a fool's. Thanks.Thu, 12 Jan 2012 17:42:04 GMTGreg Snidowhttp://www.sqlservercentral.com/Forums/Topic1234192-392-1.aspxWell 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?Thu, 12 Jan 2012 17:04:55 GMTGreg Snidowhttp://www.sqlservercentral.com/Forums/Topic1234192-392-1.aspx[quote][b]SQL Kiwi (1/11/2012)[/b][hr]Just out of interest, what are you using to connect to DB2?[/quote]PaulI'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.Wed, 11 Jan 2012 15:46:26 GMTGreg Snidowhttp://www.sqlservercentral.com/Forums/Topic1234192-392-1.aspx[quote][b]Greg Snidow (1/11/2012)[/b][hr]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.[/quote]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: [url]http://blogs.msdn.com/b/dotnetinterop/archive/2006/01/20/defining-a-db2-as-a-linked-server.aspx[/url]Wed, 11 Jan 2012 14:41:09 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic1234192-392-1.aspxThank 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.Wed, 11 Jan 2012 14:21:49 GMTGreg Snidowhttp://www.sqlservercentral.com/Forums/Topic1234192-392-1.aspx[quote][b]drew.allen (1/11/2012)[/b][hr]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.[/quote]Yes, well done.Wed, 11 Jan 2012 13:59:54 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic1234192-392-1.aspx[quote][b]SQL Kiwi (1/11/2012)[/b][hr][quote][b]Greg Snidow (1/11/2012)[/b][hr]The 'B' shows up no matter what I put in for the length parameter of CHAR. I still don't know what it means.[/quote]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.[/quote]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.DrewWed, 11 Jan 2012 13:44:56 GMTdrew.allenhttp://www.sqlservercentral.com/Forums/Topic1234192-392-1.aspx[quote][b]Greg Snidow (1/11/2012)[/b][hr]The 'B' shows up no matter what I put in for the length parameter of CHAR. I still don't know what it means.[/quote]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.Wed, 11 Jan 2012 12:57:55 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic1234192-392-1.aspxThank you Paul. I'm still a bit confused, but I did find where the 'B' is coming from, sort of...[code]DECLARE @string VARCHAR(100)SELECT @string = 0x4200000012F80C4D00000003C5144E00000000650CSELECT CONVERT(CHAR(10),@string)[/code]The 'B' shows up no matter what I put in for the length parameter of CHAR. I still don't know what it means.Wed, 11 Jan 2012 12:38:58 GMTGreg Snidowhttp://www.sqlservercentral.com/Forums/Topic1234192-392-1.aspxDB2 HEX appears to do a similar thing as using the optional style parameter of [url=http://msdn.microsoft.com/en-us/library/ms187928.aspx]CONVERT[/url] when converting from binary to a string type:[code="sql"]SELECT CONVERT(char(4), 0xDEADBEEF), -- 'Þ­¾ï' CONVERT(char(10), 0xDEADBEEF, 1), -- '0xDEADBEEF' CONVERT(char(8), 0xDEADBEEF, 2) -- 'DEADBEEF'[/code]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.Wed, 11 Jan 2012 11:52:55 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic1234192-392-1.aspxGreetings 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...[code]SELECT * FROM OPENQUERY(LinkedServer,'SELECT ID, Column1, LENGTH(Column1), HEX(Column1) AS HEX_VAL FROM Schema.Table WHERE ID = 1WITH UR;')[/code]My results look like this[code]ID Column1 HEX_VAL LENGTH1 B C200000012700CD400000003673CD500000000850C 42[/code]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...[code]SELECT TOP 1 ID, Column1, VARBIN = CAST(Column1 AS VARBINARY), LENGTH = LEN(Column1) FROM LinkedServer..Schema.Table WHERE ID = 1[/code]with results...[code]ID Column1 VARBIN LENGTH1 B 0x4200000012F80C4D00000003C5144E00000000650C 42[/code]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...[code]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)-----------------------------------results000000127000000000367300000000850[/code]... 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.Wed, 11 Jan 2012 10:40:39 GMTGreg Snidow