Merge next value from database table to a file

Nicole · Post by **Nicole** » Thu Dec 03, 2009 9:40 am

I have a sequential file as input and in the job I will be converting the sequential file into XML - one of the tags on the xml is CONFIRMATION_NMBR - to populate this number i need to retrieve from a table to get the next val. I have the sql for it and I can call it from DB2 stage - how can I continually make this call to get the nextval for each record in the sequential file and merge it to a record to add it to the XML?

Thanks for any advice

Nicole

chulett · Post by **chulett** » Thu Dec 03, 2009 10:32 am

Sounds like a lookup to me. A sparse lookup to be precise.

Nicole · Post by **Nicole** » Thu Dec 03, 2009 10:42 am

Can I do a sparse lookup with v8.0 and I can only use the DB2 UDB API stage because the database is not located on the same box as DS. We are hoping to upgrade to the new version soon - I thought we needed to upgrade in order to use this functionality - I hope not though.

chulett wrote:Sounds like a lookup to me. A sparse lookup to be precise. ...

chulett · Post by **chulett** » Thu Dec 03, 2009 10:48 am

There are sparse lookups in the 7.x release, so "yes" all around to your questions. Well, except for the "I thought we needed to upgrade" one.

Nicole · Post by **Nicole** » Thu Dec 03, 2009 1:15 pm

Ok, after this last post - I did a few more searches on this group and I think I still have a problem because I can only use the DB2 UDB API stage (since the DB is on a diff server) - I can't use the enterprise stage which gives me the option to do the sparse lookup. From what I heard, the next release that we are upgrading to - will have some sort of option for this - but until then, is there another way to do this?

Thanks again,

Nicole

chulett wrote:There are sparse lookups in the 7.x release, so "yes" all around to your questions. Well, except for the "I thought we needed to upgrade" one. ...

Nicole · Post by **Nicole** » Thu Dec 03, 2009 1:58 pm

I have a new idea and wanted to run it by everyone - the query I had only retrieved the nextval which was just the next 1 confirmation number - but I think I found a query that will return the exact number that I need immediately from the query (and if I know the number of records that I have ahead of time - which I think I will) then I can just add each row retrieved to a row in the input file - this will work right? What's the best way to join them - once I get the the numbers? they don't need to be merged by keys - it doesn't matter which number goes with which one, just as long as each next record gets the next number. So if I have 1000 records as input - then I need next 1000 numbers - then I need to merge them and insert them. Am I making sense? I hope this works!

Let me know if you can think of the best way to merge them - should I add a counter column 1,2,3...1000 to each and then merge them based on this column?

Nicole wrote:Ok, after this last post - I did a few more searches on this group and I think I still have a problem because I can only use the DB2 UDB API stage (since the DB is on a diff server) - I can't use the enterprise stage which gives me the option to do the sparse lookup. From what I heard, the next release that we are upgrading to - will have some sort of option for this - but until then, is there another way to do this?

Thanks again,

Nicole

chulett wrote:There are sparse lookups in the 7.x release, so "yes" all around to your questions. Well, except for the "I thought we needed to upgrade" one. ...