------------------------------
øM
øM
d<C

ßæC
… M

SELECT ASCII('')

-----------
8

(1 row(s) affected)

DECLARE @V VARBINARY(100) = 0x4200000012F80C4D00000003C5144E00000000650C
SELECT
  *
FROM
  dbo.AsciiToEbcdic(@V) A2E
CROSS APPLY
  dbo.GetPackedDecimal(A2E.Value, 2, 6) PD1
CROSS APPLY
  dbo.GetPackedDecimal(A2E.Value, 9, 6) PD2
CROSS APPLY
  dbo.GetPackedDecimal(A2E.Value, 16, 6) PD3
OPTION (MAXRECURSION 0)

0xC200000012700CD400000003673CD500000000850D   12700      3673      850

IF OBJECT_ID('dbo.AsciiToEbcdic', 'IF') IS NOT NULL
  DROP FUNCTION dbo.AsciiToEbcdic
GO

CREATE FUNCTION dbo.AsciiToEbcdic(@V VARBINARY(8000))
RETURNS TABLE
AS 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 
  Value
FROM 
  rCTE 
WHERE 
  I = DATALENGTH(@V)
;
GO

IF OBJECT_ID('dbo.GetPackedDecimal', 'IF') IS NOT NULL
  DROP FUNCTION dbo.GetPackedDecimal
GO

CREATE FUNCTION dbo.GetPackedDecimal(@V VARBINARY(100), @Pos INT, @Len INT)
RETURNS TABLE
AS 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)) Value
FROM
  Cte
; 
GO

--==== 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 EID
SELECT @pwd          = '#######' --<<<< change this to your mainframe password
SELECT @connection   = 'Password=' + @pwd + ';Persist Security Info=True;User ID=' + @user
SELECT @local        = '#######\' + @user --<<<< change to your domain
SELECT @LinkedServer = '#######' --<<<< change to your preference
SELECT @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;

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

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)

DECLARE @V 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

-- 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.

ALTER FUNCTION dbo.AsciiToEbcdic
(
    @v 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