Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref

Author Message
smdayaz.tpt

New User

Joined: 04 Oct 2007
Posts: 5
Location: Bangalore

Posted: Mon Jul 28, 2008 3:28 pm    Post subject: Hexadecimal Data Conversion to Integer

Hi All,

I have a Hexadecimal value in VARCHAR(2) FOR BIT DATA, I need the Hexadecimal Value to be converted to Integer.

For Eg:

 Code: DECLARE tempvar VARCHAR(4) FOR BIT DATA.  This contains value x'00005721', This needs to be converted as            (5*16^3)+(7*16^2)+(2* 16^1)+(1* 16^0) = 22305 (Integer)

Ayaz

dick scherrer

Site Director

Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

Posted: Mon Jul 28, 2008 8:42 pm    Post subject:

Hello,

Once you have read the varchar data into a variable, you could redefine the variable as a cobol binary number (COMP) and you would not need to do any calculation.

I'm not sure how this is a DB2 question. Possibly there is something i misunderstand.

If you clarify your requirement someone here may have a suggestion.
smdayaz.tpt

New User

Joined: 04 Oct 2007
Posts: 5
Location: Bangalore

Posted: Tue Jul 29, 2008 10:43 am    Post subject: Reply to: Hexadecimal Data Conversion to Integer

Hi Dick,

I think i have not clearly raised my query...Actually we are dealing in the stored Proc conversion which is coded in the SQL SERVER those PROC's needs to be converted to the DB2 and we are using the IBM DB2 Migratoin Tool kit(MKT) to do so.

There we found an issue where a variable declared in SQL server PROC has the data type of VARBINARY which the MKT converted it to the VARCHAR(4) BIT DATA which is a Equivalent data type in DB2.

The actual Problem here is, in SQLSERVER Stored PROC they CAST the VARBINARY to the INTEGER data type for the equivalent the MKT tool has to convert from the VARCHAR(4) for bit data to the Integer data type.The MKT thows an error that "The translation not succeeded" Can you give me a suggestion how we able to do this in DB2.

Here comes the CODE:

"We are facing the PROBLEM in the CASTING" I'm not sure casting is supported in DB2...

 Code: SQL SERVER STORED PROC --| CREATE PROCEDURE GetUniqueNumFromBinary --|    -- Add the parameters for the stored procedure here --|    @bindata varbinary(8) , --|    @uniquenum int output, --|    @userid tinyint output, --|    @databaseid tinyint output --| AS --| BEGIN --|    -- SET NOCOUNT ON added to prevent extra result sets from --|    -- interfering with SELECT statements. --|    SET NOCOUNT ON; --|    declare @tempunqbin varbinary(4) --|    declare @tempuserbin varbinary(2) --|    declare @tempdbbin varbinary(2) --|     -- Insert statements for procedure here --|     --|    set @tempunqbin= substring(@bindata,8,1)+substring(@bindata,7,1)+substring(@bindata,6,1)+substring(@bindata,5,1) --|    set @uniquenum = convert(int,@tempunqbin) --|     --|    set @tempuserbin = substring(@bindata,4,1)+substring(@bindata,3,1) --|    set @userid  = convert(int,@tempuserbin) --|     --|    set @tempdbbin  = substring(@bindata,2,1)+substring(@bindata,1,1) --|    set @databaseid  = convert(int,@tempdbbin) --| END DB2 Stored Proc: DROP PROCEDURE GetUniqueNumFromBinary (VARCHAR(8) FOR BIT DATA,                                        INTEGER,                                        SMALLINT,                                        SMALLINT )! CREATE PROCEDURE GetUniqueNumFromBinary (v_bindata VARCHAR(8) FOR BIT DATA,                                           OUT v_uniquenum INTEGER,                                           OUT v_userid SMALLINT,                                           OUT v_databaseid SMALLINT ) LANGUAGE SQL BEGIN         DECLARE SQLSTATE CHAR(5) DEFAULT '00000';         DECLARE l_error CHAR(5) DEFAULT '00000';         DECLARE v_tempunqbin VARCHAR(4) FOR BIT DATA;         DECLARE v_tempuserbin VARCHAR(2) FOR BIT DATA;         DECLARE v_tempdbbin VARCHAR(2) FOR BIT DATA;         DECLARE CONTINUE HANDLER FOR NOT FOUND                 SET l_error = '00000';         DECLARE CONTINUE HANDLER FOR SQLEXCEPTION, SQLWARNING                 BEGIN                         SET l_error = SQLSTATE;                         IF SUBSTR(l_error, 1, 1) >= '5'                AND SUBSTR(l_error, 1, 1) <= '9' THEN                                 RESIGNAL;                         END IF;                 END;         SET v_tempunqbin = CAST (MSSQL.SUBSTRINGBIN(v_bindata, 8, 1) AS VARCHAR(1) FOR BIT DATA) || CAST (MSSQL.SUBSTRINGBIN(v_bindata, 7, 1) AS VARCHAR(1) FOR BIT DATA) || CAST (MSSQL.SUBSTRINGBIN(v_bindata, 6, 1) AS VARCHAR(1) FOR BIT DATA) || CAST (MSSQL.SUBSTRINGBIN(v_bindata, 5, 1) AS VARCHAR(1) FOR BIT DATA);         SET v_uniquenum = CAST (v_tempunqbin AS INTEGER);         SET v_tempuserbin = CAST (MSSQL.SUBSTRINGBIN(v_bindata, 4, 1) AS VARCHAR(1) FOR BIT DATA) || CAST (MSSQL.SUBSTRINGBIN(v_bindata, 3, 1) AS VARCHAR(1) FOR BIT DATA);         SET v_userid = CAST (v_tempuserbin AS INTEGER);         SET v_tempdbbin = CAST (MSSQL.SUBSTRINGBIN(v_bindata, 2, 1) AS VARCHAR(1) FOR BIT DATA) || CAST (MSSQL.SUBSTRINGBIN(v_bindata, 1, 1) AS VARCHAR(1) FOR BIT DATA);         SET v_databaseid = CAST (v_tempdbbin AS INTEGER); END!

Ayaz
NidhiSuyog

New User

Joined: 25 Apr 2008
Posts: 1
Location: bangalore

 Posted: Tue Jul 29, 2008 11:49 am    Post subject: Reply to: Hexadecimal Data Conversion to Integer CREATE FUNCTION HexStringToINT ( @Hex varchar(255)) RETURNS int AS BEGIN declare @Char char(1), @Multiply int, @Value int, @Ans int set @Hex = reverse(@Hex) select @Ans = 0 , @Multiply = 0 while @Multiply + 1 <= datalength(@Hex) begin set @Char = substring(@Hex,@Multiply + 1,1) if @Char between '0' and '9' set @Value = cast(@Char as int) else select @Value = case @Char when 'A' then 10 when 'B' then 11 when 'C' then 12 when 'D' then 13 when 'E' then 14 when 'F' then 15 else 0 END set @Ans = @Ans + (@Value * power(16,@Multiply)) set @Multiply = @Multiply + 1 end return @Ans END GO
 All times are GMT + 6 Hours
 Page 1 of 1

Search our Forum:

 Topic Author Forum Replies Posted Similar Topics Copy 4 byte of data from the last rec... arunsoods DFSORT/ICETOOL 9 Fri Oct 06, 2017 12:15 pm opening a dataset after reading it fr... arunsoods DFSORT/ICETOOL 5 Wed Oct 04, 2017 3:54 pm PS file data should be passed as symb... d_sarlie JCL & VSAM 15 Tue Oct 03, 2017 5:18 am File Aid tool to compare numeric data balaji81_k Compuware & Other Tools 2 Tue Sep 26, 2017 3:35 am Question related to Data dictionary f... rexx77 SYNCSORT 3 Thu Aug 31, 2017 7:23 am

 © 2003-2017 IBM MAINFRAME Software Support Division
 Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us