DateTime using RowGenerator gives incorrect output

VCInDSX · Post by **VCInDSX** » Tue Mar 11, 2008 11:42 pm

I have a simple job as follows
ROW Generator ==> PEEK.

The Row Generator has 3 columns Name, ID and CallDate.
Number of records is set to 1

Name is defined as Varchar(50) and extended properties link it to a job parameter

ID is defined as Integer and the value is via job parametr.
CallDate is Date

CallDate is Date type and is also loaded via job parameter which is of type Date.

In essence, I would like to use my own values for the 3 columns.

When i run this job, the Name and ID columns show correct values for the PEEK stage in the log. The CallDate however, shows 1965-07-01 in the output log. I have setup the extended properties for the CallDate column as
Generator
\Type=Cycle
\Initial Value=#CALL_DATE_P#

The initial values from the director log for the Row Generator settings shows as follows.

Code: Select all

main_program: Explanation:
Step has 2 operators.
???, invoked with args: -schema record ( UserName: string[max=100] {cycle={value="RG-Name-Test"}};  PID: int32 {cycle={init="23"}};  CallDate: date {cycle={init="2008-03-12"}}; ) -records 1 
    output port 0 bound to data entity "Row_Generator_1:DSLink2.v"
???, invoked with args: -nrecs 10 -name 
    input port 0 bound to data entity "Row_Generator_1:DSLink2.v"
Step has 1 data entity.
  Data "Row_Generator_1:DSLink2.v"(an Orchestrate data set)
    written by operator "Row_Generator_1"
    read by operator "Peek_10"

.

The final outcome is logged as

Code: Select all

Peek_10,0: UserName:RG-Name-Test PID:23 CallDate:1965-07-01

Is there any additional setting that i might have missed, that is causing the date value to be some default that the system has?

Thanks in advance for your time

ArndW · Post by **ArndW** » Wed Mar 12, 2008 3:38 am

I recall having had difficulties with implicit date conversions before. Is your parameter of type "date"? Can you make the data column a string and then in a downstream modify or transform change perform an explicit conversion?

VCInDSX · Post by **VCInDSX** » Wed Mar 12, 2008 9:54 am

Hi ArndW,
Thanks for the followup.
I had submitted this to IBM and this is what they came up with...

this is a known issue but response from development has been that this is a debug tool and if you require you own date values to be entered then you will need to do this outside of the row generator.

Well, I wanted to generate some test data that would simulate data from suppliers on different dates. It is just that i wanted to use a predefined set of dates. I have sent a followup to IBM Support and awaiting their feedback as well. I will post an update as soon as i hear back from them.

Meanwhile, I will try the option you suggested and also the other route that Craig has used and help others with...... Attach a transformer to set the values....

Also, i will be using this logic in a different job to feed one record into an MS SQL Stored procedure.

Thanks again for your time

VCInDSX · Post by **VCInDSX** » Wed Mar 12, 2008 9:55 am

Hi ArndW,
Thanks for the followup.
I had submitted this to IBM and this is what they came up with...

this is a known issue but response from development has been that this is a debug tool and if you require you own date values to be entered then you will need to do this outside of the row generator.

Well, I wanted to generate some test data that would simulate data from suppliers on different dates. It is just that i wanted to use a predefined set of dates. I have sent a followup to IBM Support and awaiting their feedback as well. I will post an update as soon as i hear back from them.

Meanwhile, I will try the option you suggested and also the other route that Craig has used and help others with...... Attach a transformer to set the values....

Also, i will be using this logic in a different job to feed one record into an MS SQL Stored procedure.

Thanks again for your time

ray.wurlod · Post by **ray.wurlod** » Wed Mar 12, 2008 5:38 pm

That is a singularly lazy response from support!

VCInDSX · Post by **VCInDSX** » Thu Mar 13, 2008 8:26 am

Ray/Group,
The final/closure email from IBM Support is as follows.

this issue has been raised with engineering before and i have attached your reuqest to a know ecase that we had riased with
engineering.

Currently they have said there are no plans for a imdediate fix as this is only a debug/devlopment tool for random data
so if you would like to populate your own dates then you will need to do this outside of the row generator

The case number being tracked is 96505

I am working on the job where I need to send one record to the Stored Procedure using the job design.

Code: Select all

Transformer ==> Stored procedure

I will post my updates on how i fare on that front.

Thanks again for your time and help

kumar_s · Post by **kumar_s** » Thu Mar 13, 2008 3:12 pm

So again, the Transformer will require a Input.

VCInDSX · Post by **VCInDSX** » Fri Mar 14, 2008 8:52 am

Hi Kumar,
My apologies; I am not sure I follow your note.

In another thread viewtopic.php?t=117276 where I posted the results of testing the Transformer ==> PEEK in a PX job, it turned out to be a case of this approach being invalid and should be handled in a Server job.

I tried the following also.
RowGenerator ==> Transformer ==> PEEK and had the same compilation error that i posted in viewtopic.php?t=117276

From that post, it also became evident (Thanks to Ray and your reply) that the @INROWNUM logic will not hold good in a PX job without a single node config. It then became clear that multinode config will yield so many @INROWNUM as there are nodes and might not serve my purpose.

I would be willing to try any alternates approaches that might be of help.

thanks again

ArndW · Post by **ArndW** » Fri Mar 14, 2008 9:24 am

With @INROWNUM and @PARTNUM you can concoct your own unique identifier.

ray.wurlod · Post by **ray.wurlod** » Fri Mar 14, 2008 5:03 pm

Use a database stage and select one row. For example, using Oracle:
SELECT '#CallDate#','#Name#','#ID#' FROM DUAL;
(it happens that DUAL only has one row).

Or use an External Source stage with a similar echo command.

In the downstream stage in either case specify Entire partitioning so that the same row appears on all noder.

kumar_s · Post by **kumar_s** » Sat Mar 15, 2008 10:39 am

RowGenerator----->SP / ODBC

User "NumberOfRecord = 1" option in Row Generator.

kumar_s · Post by **kumar_s** » Sat Mar 15, 2008 10:51 am

Just noticed that you tried that. If you are not passing any value in those columns, just pass single Integer Dummy field.

eostic · Post by **eostic** » Sat Mar 15, 2008 4:21 pm

I believe that the best option is always going to be to attach a transformer as noted above.....there will always be some "new" datatype that you need, or other format that won't be immediately available or supported in the Row Generator. I use it all the time to generate XML strings that need to be easily tested without having to create files on disk everywhere, not to mention having Job examples that aren't dependent upon subdirectories and source file structures.

The old Server technique is no different ....either it (with Stage Variables and a constraint) or the Row Generator here, with a following Transformer, are "engines" that provide "stuff" to flow thru the links..... best if you control explicitly what "stuff" you put on those links.

Ernie

VCInDSX · Post by **VCInDSX** » Sat Mar 15, 2008 9:57 pm

Hi Group,
Thanks a lot for your invaluable time and input. Appreciate it much.
I have been trying too many things and most of what have been suggested here and could not post intermediate results. My apologies.

Long story short - with so many gurus jumping in to help me, it could never go wrong. The ride was long, but it was worth it. Thanks a lot for helping me learn so many PX concepts here.

I spent significant amount of time on the Server job to get it to work - again with your suggestions.

Then I came back to this original PX job. I had to put all these suggestions together to make it work.
[kumar] So again, the Transformer will require a Input.
[ArndW] With @INROWNUM and @PARTNUM you can concoct your own unique identifier.
[ernie] ....attach a transformer as noted above.....
[Ray] In the downstream stage in either case specify Entire partitioning so that the same row appears on all noder.
The other suggestion about using a DB Query to get data and pass it downstream was helpful in troubleshooting my server job and migrating to the PX version.

My final job looks like below.

Code: Select all

RowGenerator ==> Transformer ==> StoredProcedure

RowGenerator
One dummy integer column and not mapped to any downstream stage.
As before, "Number of Records = 1".

Transformer
Added constraint on the output link (@INROWNUM=1 And @PARTITIONNUM=1)
Linked the 3 Job parameters (Name, ID and DateValue) to the corresponding columns on the target Stored Procedure stage.
Set the "Partitioning" scheme on the input link to "Entire"
Removed the Dummy stage variable

Got this working on a Non-Grid 8.0.1 Windows Server box.

As reality was supposed to happen in a Linux GRID environment, I exported and imported into the Grid server and had zero rows on the output from transformer to Stored procedure.
The Number of partitions per node were set to 1 in the environment variable and then it occured that i should be referring the partitions using a 0-base index. I then changed the constraint on the output link to @INROWNUM=1 And @PARTITIONNUM=0 in both versions (Non-grid and grid). They worked like a charm.

Both these environments were using a 2 node config file. I would appreciate it if you could provide me some pointers to where i can find answers to the following.
When I ran it on the Windows server (Non-grid), was it pure luck that I had hit Partition-1 in my tests that made me believe that it would work?
What other (major) items should one remember when designing such solutions in the Grid environment?

Thanks again for all your time, patience and help. Could not have done it without your help

kumar_s · Post by **kumar_s** » Sun Mar 16, 2008 10:14 am

Run the job in Sequential mode. So that all stages will be running in default node single node. So that you can have desing in such a way that, RowGenerator will produce one record and the transofmer will pass the parameter to SP stage. And not to worry about partitions.