|
View previous topic :: View next topic
|
| Author |
Message |
tomehta
New User
Joined: 18 Aug 2008
Posts: 98
Location: India
|
|
|
|
Hi All,
I am doing a re-engineering project. The reenegineering involved some data base changes also. So approach followed was to create a mirror database, and run the new software on that shadow database.
To know the effectiveness of the new software, it is planned to have a do a reconcillation of two databases with same set of inputs. The database definition difference are hidden using views.
Plan to unload data from the two databases useing views (using DSNTIAUL, as unload does not work with views) and comapre the output.
I have two questions
1. How i can add a 'header' in the unlaod using views ( with DSNTIAUL) ? I want a header record which will identify the table.
2. How I can compare the two unlaod files for a table, so that I am able to tell which column values are not same for the two tables (belonging to original and mirror DB ?
regards |
|
| Back to top |
|
 |
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
Hello,
Before the unload, create a file (with the same dcb info as the unload creates) that contains the header. Then MOD the unload onto the file that contains the header.
To compare the unload files, you could run SUPERC in batch. |
|
| Back to top |
|
|
tomehta
New User
Joined: 18 Aug 2008
Posts: 98
Location: India
|
|
|
|
Hi Dick,
In SUPERC can I be able to provide the column name for the mismatched column ? I want that I should be able to pin point the coplumn which is getting wrongly populated.
To over come the header issue, I CONCATED the table name as first columns in view.
Thanks
Rohit |
|
| Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
Hello,
| Quote: |
| In SUPERC can I be able to provide the column name for the mismatched column |
Not that i'm aware of. . . I've found that if i identify mis-matches at the record level, i rarely need to show differences at the field level. When needed, i look at the 2 mis-matched records online. |
|
| Back to top |
|
|
tomehta
New User
Joined: 18 Aug 2008
Posts: 98
Location: India
|
|
|
|
Hi Dick,
I am still sruggling to find a solution to the issue. One question, regarding the following
| Quote: |
| Before the unload, create a file (with the same dcb info as the unload creates) that contains the header. Then MOD the unload onto the file that contains the header. |
I have all the columns information int he SYSPUNCH.
(
| Code: |
INSTRUMENTID POSITION( 1 )
CHAR( 24) ,
INSTRUMENTFITYP POSITION( 25 )
CHAR( 2) ,
FUNCTTYPE POSITION( 27 )
CHAR( 4) |
How can I use this syspunch to create the header which i will append at the top of the unlaod file ?
The out put will look like
(header) INSTRUMENTID INSTRUMENTFITYP FUNCTTYPE
(unlaod) XXXXXX YY UUUU
Regards |
|
| Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
Hello,
| Quote: |
| How can I use this syspunch to create the header which i will append at the top of the unlaod file ? |
Use some utility or some program code to read those records and generate the header from the column names. Be careful that the header does not exceed the length of all of the data. . .
Something that would influence how much actual work i would put into this is how many differences are to be expected. If there is supposed to be no (or very few) differences, i would not put so much effort into identifying them by some sophisticated process. . . |
|
| Back to top |
|
|
agkshirsagar
Active Member
Joined: 27 Feb 2007
Posts: 691
Location: Earth
|
|
|
|
| Quote: |
| How i can add a 'header' in the unlaod using views ( with DSNTIAUL) ? I want a header record which will identify the table |
You could create your header using simple sql like below in first step and concat with your unload to achieve desired result.
| Code: |
| SELECT 'INSTRUMENTID' 'INSTRUMENTFITYP' 'FUNCTTYPE' FROM SYSIBM.SYSDUMMY1 |
Or If you are looking for a more generic way then get the column names for a table using below query before your actual unload step.
| Code: |
SELECT NAME FROM SYSIBM.SYSCOLUMNS
WHERE TBNAME = '<your table>'
AND TBCREATOR = '<qualifier>'
|
This will give you the column names in a dataset.
Then use a DFSORT trick pdf to combine these column names on 1 row.
(Refer to the famous 'Smart DFSORT Tricks' By 'THE Frank')
Concatenate this with your actual table unload.
I won't go into the efficiency of this solution but it should give you the desired output. |
|
| Back to top |
|
|
Akatsukami
Global Moderator
Joined: 03 Oct 2009
Posts: 1788
Location: Bloomington, IL
|
|
|
|
| dick scherrer wrote: |
| I've found that if i identify mis-matches at the record level, i rarely need to show differences at the field level. |
Perhaps we're thinking of different applications (in the non-IT sense), but what if a bit flag is set wrongly in every record? |
|
| Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
Hello,
| Quote: |
| but what if a bit flag is set wrongly in every record? |
Yup. That would be identified at a very quick glance and should require one "global" fix. . . I see no need to identify this type of "difference" with some elaborate comparison/report tool.
If there were 10 million records and there 10,000 differences in 500 fields, then the field-level presentation might be worth developing. . .
Just MHO  |
|
| Back to top |
|
|
Akatsukami
Global Moderator
Joined: 03 Oct 2009
Posts: 1788
Location: Bloomington, IL
|
|
|
|
Well, perhaps the SAs in this shop are a bit less experienced than you  |
|
| Back to top |
|
|
|
|
