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)
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 http://en.wikipedia.org/wiki/EBCDIC_37. 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 http://www.simotime.com/datapk01.htm).
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:
DECLARE @v-2 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)
Output:
0xC200000012700CD400000003673CD500000000850D 12700 3673 850
Functions:
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), 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