manufacturing data

[jreddy]

Guys,

please throw suggestions as to how this can be achieved..

i need to populate a code table with values that have a domain and range specified and there is no source as such required for it. For e.g., i need to populate the table with 3 cols

1) id - can use a key generator or sequence, no problem here
2) val1 - integer of length 4 (to represent 4 digit year from 1980 to sysyear)
3) val2 - integer of length 2 (to represent month from 01 to 12)

i need to table to have entries in this fashion
1 1980 01
2 1980 02
3 1980 03
.
.
n 2004 02

thanks in advance

[ogmios]

Use Microsoft Excell to generate the data, output it to .csv format and load it with a DataStage job.

For small amounts of static data manually enter it via your favourite SQL tool. For the amount of data you need, do it manually.

:D

Ogmios

[kduke]

jreddy

Learn BASIC. Do this in job control or routine.

Code: Select all

open "DateDimHash" to DateDim else
  * need to create hash ahead of time
  stop
end
key=0
for iYear=1980 to 2004
  for jMonth=1 to 12
    realMonth=right('0':jMonth, 2)
    key += 1
    rec=''
    rec<1>=iYear
    rec<2>=realMonth
    write rec on DateDim, key else
      print 'uinable to write to DateDimHash'
      stop
    end
  next jMonth
next iYear

This should be close. To create this file you will need to a create table. Do a search. Ray has posted this many times. You will need Year as field 1 and Month as field 2. The order is important. Use a job to view the data and push it into a target table.

[jreddy]

Thank you Kim, for the code.

I did write a similar routine, but didn't know where to call it from. As i said i have no source. You mentioned that i could do it in job control. I have never written any code there for any job exept to run some UNIX commands, so i didnt exactly get the big picture. And other routines that i had written, i'd generally used them in derivations only... anyway, Assuming, the empty file was already created (assuming 'touch DateDimHash' will do). This piece of code, is it actually going to execute this loop condition and generate the data, if yes, where will it be storing it and how do i retrieve it in the job? sorry if the question seems too silly..

i will surely search some more in this forum..

thanks

[chulett]

For Server jobs with 'no source' you can play a little game, perhaps the same will work for PX.

Start your job with a Transform instead of an Active stage like OCI. Add a bogus Stage Variable (that you don't even need to use) to the Transform so the compiler won't get upset. Add a constraint using @OUTROWNUM to control how many rows flow out of the Transform, otherwise the job will spew rows out until you kill it. For example, to get a single row use a constraint of "@OUTROWNUM < 2" or "@OUTROWNUM = 1", whatever tickles your fancy. Call your routine.

I've used this a number of times to 'manufacture data'... you probably wouldn't even need your routine. Use some real Stage Variables to generate the data you need.

Heck, you can do this directly in Oracle if you wanted. Google for something called "Markov Chaining", the above is a variation of that.

[ray.wurlod]

Does that technique work in the PX environment if you have more than one data partition?
Yes, you could work around it. Obviously you should use a configuration file specifying just one processing node for this job.

[kduke]

Craig

A batch job has no stages. You could do this as a batch job. A batch job does not have to execute other jobs. If I did this a routine then I would use it as a before routine of the job that took the hash file and pushed it into a table. I prefer a batch job with the begin year and end year as parameters.

With a few oconv() you can build a date dimension with day of week like Wednesday or February for a Month spelled out. All those columns which make it a textbook perfect date dimension.

When your open fails then you could execute a create table and open it again. This makes this routine complete. If you need the code then let me know.