Getting the source file & table count
Moderators: chulett, rschirm, roy
Getting the source file & table count
Hi All
I searched a lot but could not find the right path.
so posting here
question is: how to get the count if file is source or if the source is table.
And also i need to get the target count.
I want to use these counts in the onther job.
Need your help on this..
thanks a lot..
I searched a lot but could not find the right path.
so posting here
question is: how to get the count if file is source or if the source is table.
And also i need to get the target count.
I want to use these counts in the onther job.
Need your help on this..
thanks a lot..
Re: Getting the source file & table count
If the source is a file you can use something like this in an Execute Command stage within a Job Sequence and pass it as a parameter to the job you would like.
If the source is a table, the way I have done it in the past is to develop a shell script that connects to the database and get the count from the table and returns the value at the end. The shell script was executed through Execute Command Stage and the Command Output was passed as a parameter to the job you need.
Hope that helps.
Code: Select all
ls -1t filename | xargs wc -l | awk '{ printf ("%d", ($1-1))}'
Hope that helps.
Kris
Where's the "Any" key?-Homer Simpson
Where's the "Any" key?-Homer Simpson
I really believe all ETL projects should have completeness reports. I wrote a blog about it. I have web pages on how to do it. Basically you need a report comparing source counts to target counts for each target table.
I give the jobs away to build this. It is part of EtlStats. The reports come out very nice. The SQL to count the source and target is stored in a table. The results in another table and report definition in another table.
http://www.duke-consulting.com/Setting_ ... eports.htm
http://it.toolbox.com/blogs/object-orie ... orts-35391
Code: Select all
target table name | Target Count | Source Count | Percent |
---------------------------------------------------------------------
CustomerDim | 997 | 1000 | 99.70 |
OrderTypeDim | 3 | 3 | 100.00 |
---------------------------------------------------------------------
http://www.duke-consulting.com/Setting_ ... eports.htm
http://it.toolbox.com/blogs/object-orie ... orts-35391
Mamu Kim
Re: Getting the source file & table count
Hello Kris
Thanks a lot for your info
But just 1 question when we execute the shell script from the Execute command stage we will get the connection information also in the command output how to remove that please...I want only count in the output command.
Thanks a lot for your info
But just 1 question when we execute the shell script from the Execute command stage we will get the connection information also in the command output how to remove that please...I want only count in the output command.
kris007 wrote:If the source is a file you can use something like this in an Execute Command stage within a Job Sequence and pass it as a parameter to the job you would like.If the source is a table, the way I have done it in the past is to develop a shell script that connects to the database and get the count from the table and returns the value at the end. The shell script was executed through Execute Command Stage and the Command Output was passed as a parameter to the job you need.Code: Select all
ls -1t filename | xargs wc -l | awk '{ printf ("%d", ($1-1))}'
Hope that helps.
What do you mean by connection information?
Above command mentioned by Kris will give only the number of lines in a file.
This command also gives just the number of lines
Above command mentioned by Kris will give only the number of lines in a file.
This command also gives just the number of lines
Code: Select all
printf $(wc -l <filename)
You are the creator of your destiny - Swami Vivekananda
Re: Getting the source file & table count
There is no shell script involved. You just have to use the command within the Execute Command stage. Make sure you give the file path correctly within the Execute Command Stage.dssiddu wrote:Hello Kris
But just 1 question when we execute the shell script from the Execute command stage we will get the connection information also in the command output how to remove that please...I want only count in the output command.
Kris
Where's the "Any" key?-Homer Simpson
Where's the "Any" key?-Homer Simpson
Re: Getting the source file & table count
HI Kris
thanks, But if my source is table then i need to call 1 script in that i need to connect db then i will get some record count as the output command along with the db connection information.It does't give the only number to output command.
Hope its clear..
Thanks
thanks, But if my source is table then i need to call 1 script in that i need to connect db then i will get some record count as the output command along with the db connection information.It does't give the only number to output command.
Hope its clear..
Thanks
kris007 wrote:There is no shell script involved. You just have to use the command within the Execute Command stage. Make sure you give the file path correctly within the Execute Command Stage.dssiddu wrote:Hello Kris
But just 1 question when we execute the shell script from the Execute command stage we will get the connection information also in the command output how to remove that please...I want only count in the output command.
Re: Getting the source file & table count
dssiddu wrote:HI Kris
thanks, But if my source is table then i need to call 1 script in that i need to connect db then i will get some record count as the output command along with the db connection information.It does't give the only number to output command. Basically i need to use the output of the script in the execute command in the next stage in the seq.
Hope its clear..
Thanks
kris007 wrote:There is no shell script involved. You just have to use the command within the Execute Command stage. Make sure you give the file path correctly within the Execute Command Stage.dssiddu wrote:Hello Kris
But just 1 question when we execute the shell script from the Execute command stage we will get the connection information also in the command output how to remove that please...I want only count in the output command.
Re: Getting the source file & table count
Yes it will. As long as you echo only the COUNT at the end of the script. All other informational messages should be written to the log file. Something along these lines:
Code: Select all
connection=`db2 connect to Database user user using password`
if [[ $? -ne 0 ]]; then
echo "\n Error Connecting to the Database \n" >> $logfilename
exit 3
fi
COUNT=`db2 -x "SELECT COUNT(*) FROM TABLE"`
echo $COUNT
Kris
Where's the "Any" key?-Homer Simpson
Where's the "Any" key?-Homer Simpson