January 13, 2012 at 1:32 pm
Peter, 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...
USE master
GO
IF OBJECT_ID('dbo.fnAsciiToEbcdic','fn') IS NOT NULL
DROP FUNCTION dbo.fnAsciiToEbcdic
GO
CREATE 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 @return
END
And now the lookup table...
USE master
GO
IF OBJECT_ID('master..ASCII_EBCDIC','u') IS NOT NULL
DROP TABLE master..ASCII_EBCDIC
GO
CREATE TABLE master..ASCII_EBCDIC
(
ID INT IDENTITY(1,1),
ASCII_VAL BINARY,
EBCDIC_VAL BINARY
)
INSERT INTO master..ASCII_EBCDIC
VALUES
(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)
GO
IF EXISTS(SELECT * FROM SYSINDEXES WHERE name = 'IX_MASTER_ASCII_EBCDIC_ascii_val')
DROP INDEX ASCII_EBCDIC.IX_MASTER_ASCII_EBCDIC_ascii_val
GO
CREATE UNIQUE CLUSTERED INDEX IX_MASTER_ASCII_EBCDIC_ascii_val
ON ASCII_EBCDIC(ascii_val)
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
January 13, 2012 at 7:46 pm
Peter Brinkhaus (1/13/2012)
What's the data type of Column1 one in DB2? It looks like a structure containing 6 columns...
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! 🙂
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
January 14, 2012 at 12:43 pm
Paul, 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,
Peter
January 14, 2012 at 1:05 pm
Greg Snidow (1/13/2012)
Peter, I looked at the link you provided for the lookup table, and decided to create a permanent table to use in the function.
That's a good thing to do.
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.
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:
DECLARE @v-2 VARBINARY(100) = 0x4200000012F80C4D00000003C5144E00000000650C
SET STATISTICS TIME ON
PRINT '-- Scalar function'
select top 10000
dbo.fnAsciiToEbcdic(@V) V
from
sys.columns C1
cross join
sys.columns C2
PRINT '-- Recursive CTE'
SELECT TOP 10000
A2E.*
FROM
sys.columns C1
CROSS JOIN
sys.columns C2
CROSS APPLY
dbo.AsciiToEbcdic(@V) A2E
SET STATISTICS TIME OFF
Result:
-- 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.
Anyway, thanks for the very positive feedback.
Peter
January 14, 2012 at 1:54 pm
Peter Brinkhaus (1/14/2012)
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).
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...http://blogs.msdn.com/b/sqltips/archive/2005/07/20/441053.aspx
Here's a quick test
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:
ALTER FUNCTION dbo.AsciiToEbcdic
(
@v-2 VARBINARY(8000)
)
RETURNS TABLE
WITH SCHEMABINDING AS
RETURN
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)
SELECT
ate.result
FROM dbo.AsciiToEbcdic
(0x4200000012F80C4D00000003C5144E00000000650C) AS ate
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
Viewing 5 posts - 16 through 19 (of 19 total)
You must be logged in to reply to this topic. Login to reply