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
manufacturing data
Moderators: chulett, rschirm, roy
Re: manufacturing data
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
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
jreddy
Learn BASIC. Do this in job control or routine.
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.
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
Mamu Kim
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
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
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.![Cool 8)](./images/smilies/icon_cool.gif)
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.
![Cool 8)](./images/smilies/icon_cool.gif)
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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.
![Confused :?](./images/smilies/icon_confused.gif)
Yes, you could work around it. Obviously you should use a configuration file specifying just one processing node for this job.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
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.
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.
Mamu Kim