loading the first record only

sweetleaf · Post by **sweetleaf** » Thu Feb 27, 2003 7:48 am

Hi there,

I have a sequential file, transform and oracle stage.
I want to load only the first record from the sequential file into my oracle table.
Is there a way to do this?

Thanks!

trobinson · Post by **trobinson** » Thu Feb 27, 2003 8:40 am

Use @INROWNUM < 2 as a contstraint on an output link (input link to you Oracle stage. Only the first row will meet the constraint.

mihai · Post by **mihai** » Thu Feb 27, 2003 8:53 am

What trobinson said or @OUTROWLINK

vmcburney · Post by **vmcburney** » Thu Feb 27, 2003 10:51 pm

When you run a job from the director you are presented with a screen that asks how many rows you want to process. You can limit the job to just one row by setting this number to 1. If you call it up from a Batch I believe you can also set this value to process just one row.

Don't have the DataStage software in front of me right now so I can't tell you exactly where these options are.

Vincent McBurney
Data Integration Services
www.intramatix.com

ray.wurlod · Post by **ray.wurlod** » Thu Feb 27, 2003 11:23 pm

Limiting the rows to be processed leaves your job in a Stopped state. It may require to be reset.

Another approach to this is to pre-process the file, via ExecSH (on UNIX). For example, if the file whose first row only is what you want is called file1, then run the command "head -1 file1 > file2" via ExecSH then have DataStage process file2.

Ray Wurlod
Education and Consulting Services
ABN 57 092 448 518

vmcburney · Post by **vmcburney** » Fri Feb 28, 2003 3:05 am

Tested a job reading and writing a sequential file with the "Stop Stages after" set to 1 rows and it worked well. Processed the row and returned to a Finished state.

writecsv..CTransformerStage1: DSD.StageRun Active stage finishing.
1 rows read from DSLink3
1 rows written to DSLink4

The bonus here is that you can use the same job for a single row that you would use for the entire file. From a routine or batch job you can use the DSSetJobLimit function with the limit type of DSJ.LIMITROWS.

Vincent McBurney
Data Integration Services
www.intramatix.com

sweetleaf · Post by **sweetleaf** » Fri Feb 28, 2003 9:19 am

Hi there,

Thanks - taking your advice, i set the row limit in the Director. It works fine.

Can I use @INROWNUM > 2 to "skip" the header info in my file?

Thanks.

chulett · Post by **chulett** » Fri Feb 28, 2003 9:48 am

Yes if the first *two* records are header information. If there is only one you could just set the properties correctly in the Sequential stage. [:)]

On the Format tab is a check-box for "First line is column names", select that to automagically skip the first record.

-craig

sweetleaf · Post by **sweetleaf** » Sat Mar 01, 2003 12:47 pm

Hi there,

I tried typing just @INROWNUM > 2 in the constraint section of my transform stage (which reads data IN from a sequential file OUT to an oracle table), and it did not work.

Is there something else which I have left out?

I'd like to skip the first 2 records of this text file and begin importing at row/line 3. Can this be done?

Thanks.

ray.wurlod · Post by **ray.wurlod** » Sat Mar 01, 2003 6:46 pm

Yes. Definitely. Your constraint expression (@INROWNUM > 2) is definitely OK. What symptom led to the assertion you made that "it didn't work"?

Ray Wurlod
Education and Consulting Services
ABN 57 092 448 518

vmcburney · Post by **vmcburney** » Sat Mar 01, 2003 9:43 pm

Here's where it gets confusing! You've specified in your run options that you only want to process one row. You have a constraint that halts the first two rows at the transformer stage. You are processing just one row and it is being rejected by your constraint. You need to up the number of rows processed to 3 to ensure your constraint rejects the first 2 rows and you get the 3rd row processed.

Vincent McBurney
Data Integration Services
www.intramatix.com