DSXchange

Posted: **Wed Apr 22, 2015 4:53 am**

Hi Friends

We have migrated to a new server.

After migration,We are facing a strange issue

We are using below code to extract day of week from a date.

ToChar('2014-10-01',"D")

It is returning value as 3 in old server where same code is returning 4 on new server.

The values of DataStage environment variables are same in old and new environment

Any ideas,what could be causing this

Any option changed during installation can cause this ?

Appreciate your help

Posted: **Wed Apr 22, 2015 7:04 am**

That's not a DataStage function.

Since it's a new server, that probably means you have a new database client installation.

You'll need to check your database client configuration settings for differences. Enlist the help of your DBA if necessary.

Mike

Posted: **Wed Apr 22, 2015 7:05 am**

Not aware of any "ToChar" function so it must be something unique to your site... what is it actually using inside it? Off the top of my head there are settings (a setting?) that controls what day of the week is considered day 1, Sunday or Monday... you may be running afoul of that. LOCALE perhaps? Hopefully someone will know for certain.

In the meantime might be prudent to post your routine code.

Posted: **Wed Apr 22, 2015 7:17 am**

A little more context please...

I should've said it wasn't an out-of-the-box DataStage function.

I assumed it to be a SQL function, while Craig guessed that it might possibly be a custom server routine.

Mike

Posted: **Wed Apr 22, 2015 8:14 am**

Yes. you all are right. Its not a DataStage Function. Its a server routine

Below is the code

Code: Select all

      Dimension f(23), v(23)

      datepart=Field(date," ", 1)
      timepart=Field(date," ", 2)

* dy=OConv(IConv(datepart, "D-YMD[4,2,2]"),"DWB")
* day=OConv(IConv(datepart, "D-YMD[4,2,2]"),"DWA")

* month=OConv(IConv(datepart, "D-YMD[4,2,2]"),"DMA")
* mon=OConv(IConv(datepart, "D-YMD[4,2,2]"),"DMB")


      dd=date[9,2]
      mm=date[6,2]
      year=date[1,4]
      hour=date[12,2]
      minute=date[15,2]
      second=date[18,2]

      f(1)="DDD"
      f(2)="DD"
      f(3)="DY"
      f(4)="DAY"
      f(5)="D"

      f(6)="MONTH"
      f(7)="MON"
      f(8)="MM"

      f(9)="YYYY"
      f(10)="YYY"
      f(11)="YY"
      f(12)="Y"

      f(13)="WW"
      f(14)="W"


      f(15)="HH24"
      f(16)="HH12"
      f(17)="HH"

      f(18)="MI"
      f(19)="Q"
      f(20)="RR"
      f(21)="SSSSS"
      f(22)="SS"

      f(23)="J"

      v(1)=OConv(IConv(datepart, "D-YMD[4,2,2]"),"DJ")

      If Len(v(1)) = 2 Then
         v(1)="0":v(1)
      End

      If Len(v(1)) = 1 Then
         v(1)="00":v(1)
      End

      v(2)=date[9,2]
      v(3)=OConv(OConv(IConv(datepart, "D-YMD[4,2,2]"),"DWB"), "MCT")
      v(4)=OConv(OConv(IConv(datepart, "D-YMD[4,2,2]"),"DWA"), "MCT")

      v(5)=Int(OConv(IConv(datepart, "D-YMD[4,2,2]"),"DW"))



      v(6)=OConv(OConv(IConv(datepart, "D-YMD[4,2,2]"),"DMA"), "MCT")
      v(7)=OConv(OConv(IConv(datepart, "D-YMD[4,2,2]"),"DMB"), "MCT")
      v(8)=date[6,2]

      v(9)=date[1,4]
      v(10)=date[2,3]
      v(11)=date[3,2]
      v(12)=date[4,1]

      If mod(Int(OConv(IConv(datepart, "D-YMD[4,2,2]"),"DJ")), 7) <> 0 Then
         v(13)=Int(OConv(IConv(datepart, "D-YMD[4,2,2]"),"DJ")/7)+1
      End Else
         v(13)=Int(OConv(IConv(datepart, "D-YMD[4,2,2]"),"DJ")/7)
      End

      If mod(Int(date[9,2]), 7) <> 0 Then
         v(14)=Int(date[9,2]/7)+1
      End Else
         v(14)=Int(date[9,2]/7)
      End

      hour=date[12,2]
      minute=date[15,2]
      second=date[18,2]

      v(15)=date[12,2]

      v(16)=date[12,2]

      If Int(date[12,2]) > 12 Then
         v(16)=mod(Int(date[12,2]), 12)
      End

      If Int(date[12,2]) = 0 Then
         v(16)="12"
      End


      v(17)=date[12,2]

      If Int(date[12,2]) > 12 Then
         v(17)=mod(Int(date[12,2]), 12)
      End

      If Int(date[12,2]) = 0 Then
         v(17)="12"
      End

      v(18)=date[15,2]
      v(19)=OConv(IConv(datepart, "D-YMD[4,2,2]"),"DQ")

      v(20)=date[3,2]
      v(21)=(Int(date[12,2])*60*60)+(Int(date[15,2])*60)+Int(date[18,2])
      v(22)=date[18,2]

      v(23)="J"


      i=1

      OutputDate=format

      For i = 1 To 23 Step 1
         OutputDate=Change(OutputDate, f(i), v(i))
      Next i

      Ans=OutputDate

Posted: **Wed Apr 22, 2015 9:26 am**

Yikes. Looks like massive overkill just to get the day of the week but I guess it was meant to be generic and one size fits all. Still.

Where's the argument handling? I don't see any mention of Arg1 or Arg2, guessing "date" is Arg1 but no clue where the "D" you were passing in goes... "format"? Is the posted code the complete code?

Posted: **Wed Apr 22, 2015 12:48 pm**

Still think it has something to do with your locale setting or something of that nature, whatever OConv uses to determine what day your week starts on. 2014-10-01 was a Wednesday, btw, hence the 3 or 4 depending on your week starting on Monday or Sunday.

Clarify something, please - what is the difference between the "old server" and the "new server"? Anything significant like the O/S flavor or version? Hardware vendor? DataStage version?

Posted: **Thu Apr 23, 2015 12:14 am**

Hi,
I am team member of user who posted this topic.

Just to give an update:

The function OConv(IConv(date, "D-YMD[4,2,2]"),"DW") is also returning value as 4 in new environment while in old it is returning 3 when we are passing the date as '2014-10-01'.

Even for the other dates the value is always 1 higher in the new environment.
The Datastage Version,Hardware is same in both the environments,just that the new one is installed on a different server.

Do we have any setting in DataStage which decides whether Sunday should be considered 0 or 1.

Appreciate your help.

Posted: **Thu Apr 23, 2015 12:35 am**

There is, and how that is done depends on whether or not you have NLS enabled.

If you have NLS enabled, then the ordinal day number settings are specified in your locale. Without NLS there is, if I recall correctly, an undocumented setting in the uvconfig file.

Or you could simply subtract 1 from the value, or use a Convert() function to achieve the required wraparound.

You can also use the weekday_from_date() function in the Modify stage, which lets you specify which is the start day (the day zero) of the week.

Posted: **Thu Apr 23, 2015 7:28 am**

sharmaisha0902 wrote:The Datastage Version,Hardware is same in both the environments,just that the new one is installed on a different server.

Sounds like one might have NLS enabled and the other doesn't.

Posted: **Fri Apr 24, 2015 12:21 am**

Hi Friends

Regarding the NLS, yes you are right.

We didn't had it enabled on old server however NLS is enabled on new server.

Is it possible that we can disable NLS now or is this option configurable only at the time of installation and can't be changed later on ?

Thanks all for your help

Posted: **Fri Apr 24, 2015 4:20 am**

You cannot change the fact that NLS is installed.

However, the NLSMODE parameter in the uvconfig file determines whether NLS is enabled or not.

The usual caveats apply, such as the need to reconfigure shared memory during a restart of the engine having changed the uvconfig file.

Posted: **Mon May 18, 2015 2:40 am**

Hi All

For this issue, we raised PMR with IBM.

IBM support suggested to disable the DATE/TIME Server locale to OFF in DataStage admin client.

This solved the issue

Thanks all for your help.

DSXchange

issues with To_CHAR function

issues with To_CHAR function

Posting on behalf of udayk_2007

Re: Posting on behalf of udayk_2007