issues with To_CHAR function

Post questions here relative to DataStage Server Edition for such areas as Server job design, DS Basic, Routines, Job Sequences, etc.

Moderators: chulett, rschirm, roy

Post Reply
udayk_2007
Participant
Posts: 72
Joined: Wed Dec 12, 2007 2:29 am

issues with To_CHAR function

Post by udayk_2007 »

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
Mike
Premium Member
Premium Member
Posts: 1021
Joined: Sun Mar 03, 2002 6:01 pm
Location: Tampa, FL

Post by Mike »

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
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

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

"You can never have too many knives" -- Logan Nine Fingers
Mike
Premium Member
Premium Member
Posts: 1021
Joined: Sun Mar 03, 2002 6:01 pm
Location: Tampa, FL

Post by Mike »

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
udayk_2007
Participant
Posts: 72
Joined: Wed Dec 12, 2007 2:29 am

Post by udayk_2007 »

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
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

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?
-craig

"You can never have too many knives" -- Logan Nine Fingers
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

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?
-craig

"You can never have too many knives" -- Logan Nine Fingers
sharmaisha0902
Participant
Posts: 4
Joined: Fri Sep 02, 2011 9:56 am

Posting on behalf of udayk_2007

Post by sharmaisha0902 »

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.
Thanks,
Isha Sharma
ray.wurlod
Participant
Posts: 54607
Joined: Wed Oct 23, 2002 10:52 pm
Location: Sydney, Australia
Contact:

Post by ray.wurlod »

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.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Re: Posting on behalf of udayk_2007

Post by chulett »

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

"You can never have too many knives" -- Logan Nine Fingers
udayk_2007
Participant
Posts: 72
Joined: Wed Dec 12, 2007 2:29 am

Post by udayk_2007 »

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
ray.wurlod
Participant
Posts: 54607
Joined: Wed Oct 23, 2002 10:52 pm
Location: Sydney, Australia
Contact:

Post by ray.wurlod »

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.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
udayk_2007
Participant
Posts: 72
Joined: Wed Dec 12, 2007 2:29 am

Post by udayk_2007 »

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