Issue with IsValid function with integer(int32) and decimal

[sanjumsm]

Hi
Can anybody help me on following issue.

I am using following funtion to validate integer values.
IsValid("Int32","+2147483648") this retrun 1 even though the max positive value allowed is 2147483647.

another issue:
I am validating decimal[18,2] values using following function in transformer. IsValid("decimal[18,2]","19.333") . Since scale exceed the length defined IsValid function it should return 0 but it is returinh 1.

Please help me on the above issues.

Thanks

[qt_ky]

For your second question, I did not think you could put precision/scale into decimal for IsValid(). The description of the function does not go into much depth or provide many examples. Have you tried IsValidDecimal()?

[qt_ky]

IsValid("Int32","+2147483648") this retrun 1 even though the max positive value allowed is 2147483647.

I think you may have found a bug. I've confirmed it in version 8.7.

IsValid('int32', 2147483647) returns 1.
IsValid('int32', 2147483648) returns 1. It should return 0.
IsValid('int32', 2147483649) returns 0.

IsValid() on int8 and int16 upper limits gives expected results. On int32 and int64 it appears to give incorrect results. On int64, it's worse in that as the numbers continue to increase beyond the int64 upper limit, IsValid() continues to return 1. Maybe you can confirm that too. I wonder if IsValid() has any issues with the lower limits as well.

There is an IBM tech note about IsValid()'s changed behavior starting in 8.1. It mentions an environment variable, APT_ISVALID_75COMPAT, which you can add and set to restore 7.5.2 functionality (it's more strict).

JR39651: DATASTAGE FUNCTION ISVALID("INT64",COLUMN) HAS DIFFERENT RESULTS IN DATASTAGE 7.5 AND INFORMATION SERVER 8.5 AND 8.1.

http://www-01.ibm.com/support/docview.w ... wg1JR39651

If you confirm it, are you going to report it to IBM? If so, post what you find out from them, if it is a bug or not.

[Sivacharan Irukulla]

I have just tried giving 2147483648 to the IsValid(), and the result is as expected.

Which version of Datastage you are using? , I am not sure about the previous versions of datastage. But, I have implemented it in Datastage 8.X and is working fine .


IsValid('int32', '2147483648') returning zero in the output.

[qt_ky]

As I stated above, I confirmed a wrong result on version 8.7 (version 8.7.0.0 64 bit on AIX). It returned a 1 for me... So you are on 8.x? What version of x?

[Sivacharan Irukulla]

I have implemented it on 8.1v

[Sivacharan Irukulla]

I have implemented it on 8.0.1v

[qt_ky]

The IBM tech note says that the IsValid() behavior changed starting in 8.1.

Here's what I see on 8.7.0.0 (64 bit) on AIX using a simple test of Row Gen -> Xfm -> Peek, without having APT_ISVALID_75COMPAT or any other special environment variables set. This tests IsValid() on the upper limits of various signed integer data types.

peek,0: A:IsValid('int8', 125)=1 B:IsValid('int16', 32765)=1 C:IsValid('int32', 2147483645)=1 D:IsValid('int64', 9223372036854775805)=1
peek,0: A:IsValid('int8', 126)=1 B:IsValid('int16', 32766)=1 C:IsValid('int32', 2147483646)=1 D:IsValid('int64', 9223372036854775806)=1
peek,0: A:IsValid('int8', 127)=1 B:IsValid('int16', 32767)=1 C:IsValid('int32', 2147483647)=1 D:IsValid('int64', 9223372036854775807)=1
peek,0: A:IsValid('int8', 128)=0 B:IsValid('int16', 32768)=0 C:IsValid('int32', 2147483648)=1 D:IsValid('int64', 9223372036854775808)=1
peek,0: A:IsValid('int8', 129)=0 B:IsValid('int16', 32769)=0 C:IsValid('int32', 2147483649)=0 D:IsValid('int64', 9223372036854775809)=1
peek,0: A:IsValid('int8', 130)=0 B:IsValid('int16', 32770)=0 C:IsValid('int32', 2147483650)=0 D:IsValid('int64', 9223372036854775810)=1

My assumption is that the upper limit of a signed integer of N bits is (2^(N-1) - 1). The first 3 lines' results should be 1. The last 3 lines' results should be 0. If it's colored red then I think it's an unexpected, incorrect result. Anyone want to compare results and double check if the values and assumptions are correct or not?

[sanjumsm]

Thank you everybody for giving their feedback.

I am using 8.1 version of datastage. I wanted to use APT_ISVALID_75COMPAT parameter but unfortunately i can't see that in environment parameter list. But i cross checked the behaviour of IsValid for "Int32" in 7.5 and it is working fine there. So the issue is only with 8.X versions of datastage.

For my second issue of validating decimal values in IsValid function:
I tried removing precision and scale from from IsValid function "IsValid("decimal","19.333")" but it failed with error "APT_CombinedOperatorController,0: Parsing parameters "" for schema type "decimal": Expected precision; got: <eof>".
I even searched for IsValidDecimal() but i didn't find any such function in 8.1.

Is there any function to check the scale in decimal values.

Looking forward for your advice.

Thanks..

[chulett]

I wanted to use APT_ISVALID_75COMPAT parameter but unfortunately i can't see that in environment parameter list.

If you've applied the 'fix' that added that option and you still don't see it in the list, I would simply add it manually - type it in by hand. See if it helps.

[qt_ky]

I even searched for IsValidDecimal() but i didn't find any such function in 8.1.

Is there any function to check the scale in decimal values.

Version 8.7 includes the following IsValid* functions under the type conversion category. I don't know what version each function was first introduced, so if you don't have them in 8.1, you could contact support to ask, or just convert your values to a string and create your own test using Index() and the like.

IsValid()
IsValidDate()
IsValidDecimal()
IsValidTime()
IsValidTimestamp()