DB2 z/OS Schema Compare Tools

Bill Gallagher

DB2 z/OS Schema Compare Tools
Our environment: DB2 z/OS v11

After a couple of months on the bench, I'm back in the game and working a contract position.

We're lacking in mainframe DB2 administration tools here, so I've been tasked with looking for something that can perform schema comparisons between two databases, such as comparing production to one of our test databases, or comparing two test databases to each other. Basically, something that can look for things such as missing or differences in columns, triggers, foreign keys, indexes, etc.

The only mainframe DB2 tool we have is File Manager for DB2, which doesn't seem to have this functionality from what I can tell. Then again, I'm still trying to learn it, so maybe I haven't found it yet.

Purchasing something like the BMC or CA tools for DB2 z/OS is not an option due to cost.

So I'm looking for relatively inexpensive but functional desktop options. I believe CA ERWin may be able to do this, but I don't have a copy of it (yet).

Looking for other options for tools that perform this kind of functionality. DB Artisan from Idera (formerly from Embarcadero) looks promising. Aqua Data Studios from Aquafold may also be an option.

Are there any other tools that I should look at? Or can anybody share their suggestions and/or experiences with the other tools mentioned above?

Thanks.

Bill Gallagher
DB2 Database Administrator
State of Connecticut
[login to unmask email]<mailto:[login to unmask email]>

Sam Baugh

DB2 z/OS Schema Compare Tools
(in response to Bill Gallagher)
AQT is fairly inexpensive (Advanced Query Tool), and great support. It
will required having DB2 Connect also.

On Tue, Oct 3, 2017 at 12:52 PM, GALLAGHER, WILLIAM <[login to unmask email]>
wrote:

> Our environment: DB2 z/OS v11
>
>
>
> After a couple of months on the bench, I’m back in the game and working a
> contract position.
>
>
>
> We’re lacking in mainframe DB2 administration tools here, so I’ve been
> tasked with looking for something that can perform schema comparisons
> between two databases, such as comparing production to one of our test
> databases, or comparing two test databases to each other. Basically,
> something that can look for things such as missing or differences in
> columns, triggers, foreign keys, indexes, etc.
>
> The only mainframe DB2 tool we have is File Manager for DB2, which doesn’t
> seem to have this functionality from what I can tell. Then again, I’m
> still trying to learn it, so maybe I haven’t found it yet.
>
> Purchasing something like the BMC or CA tools for DB2 z/OS is not an
> option due to cost.
>
> So I’m looking for relatively inexpensive but functional desktop options.
> I believe CA ERWin may be able to do this, but I don’t have a copy of it
> (yet).
>
> Looking for other options for tools that perform this kind of
> functionality. DB Artisan from Idera (formerly from Embarcadero) looks
> promising. Aqua Data Studios from Aquafold may also be an option.
>
>
>
> Are there any other tools that I should look at? Or can anybody share
> their suggestions and/or experiences with the other tools mentioned above?
>
>
>
> Thanks.
>
>
>
> Bill Gallagher
>
> DB2 Database Administrator
>
> State of Connecticut
>
> [login to unmask email]
>
>
>
> -----End Original Message-----
>

Nadir Doctor

DB2 z/OS Schema Compare Tools
(in response to Sam Baugh)
Hi Bill,

Fyi, Idera has recently acquired Aquafold -

https://www.idera.com/about/news/pressreleases/2017/idera-acquires-aquafold-to-expand-database-tools-business


Best Regards,
Nadir


On Tue, Oct 3, 2017 at 1:16 PM, Sam Baugh <[login to unmask email]> wrote:

> AQT is fairly inexpensive (Advanced Query Tool), and great support. It
> will required having DB2 Connect also.
>
> On Tue, Oct 3, 2017 at 12:52 PM, GALLAGHER, WILLIAM <[login to unmask email]>
> wrote:
>
>> Our environment: DB2 z/OS v11
>>
>>
>>
>> After a couple of months on the bench, I’m back in the game and working a
>> contract position.
>>
>>
>>
>> We’re lacking in mainframe DB2 administration tools here, so I’ve been
>> tasked with looking for something that can perform schema comparisons
>> between two databases, such as comparing production to one of our test
>> databases, or comparing two test databases to each other. Basically,
>> something that can look for things such as missing or differences in
>> columns, triggers, foreign keys, indexes, etc.
>>
>> The only mainframe DB2 tool we have is File Manager for DB2, which
>> doesn’t seem to have this functionality from what I can tell. Then again,
>> I’m still trying to learn it, so maybe I haven’t found it yet.
>>
>> Purchasing something like the BMC or CA tools for DB2 z/OS is not an
>> option due to cost.
>>
>> So I’m looking for relatively inexpensive but functional desktop
>> options. I believe CA ERWin may be able to do this, but I don’t have a
>> copy of it (yet).
>>
>> Looking for other options for tools that perform this kind of
>> functionality. DB Artisan from Idera (formerly from Embarcadero) looks
>> promising. Aqua Data Studios from Aquafold may also be an option.
>>
>>
>>
>> Are there any other tools that I should look at? Or can anybody share
>> their suggestions and/or experiences with the other tools mentioned above?
>>
>>
>>
>> Thanks.
>>
>>
>>
>> Bill Gallagher
>>
>> DB2 Database Administrator
>>
>> State of Connecticut
>>
>> [login to unmask email]
>>
>>
>>
>> -----End Original Message-----
>

Bill Gallagher

DB2 z/OS Schema Compare Tools
(in response to Nadir Doctor)
Nadir,

Thanks for passing this on. This is interesting. With two products that were somewhat competing against each other (DB Artisan and Aqua Data Studio), I wonder which one will be chosen by Idera to survive going forward?

Bill Gallagher
DB2 Database Administrator
State of Connecticut
[login to unmask email]<mailto:[login to unmask email]>


From: Nadir Doctor [mailto:[login to unmask email]
Sent: Tuesday, October 03, 2017 3:30 PM
To: [login to unmask email]
Subject: [DB2-L] - RE: DB2 z/OS Schema Compare Tools

Hi Bill,

Fyi, Idera has recently acquired Aquafold -

https://www.idera.com/about/news/pressreleases/2017/idera-acquires-aquafold-to-expand-database-tools-business


Best Regards,
Nadir


On Tue, Oct 3, 2017 at 1:16 PM, Sam Baugh <[login to unmask email]<mailto:[login to unmask email]>> wrote:
AQT is fairly inexpensive (Advanced Query Tool), and great support. It will required having DB2 Connect also.

On Tue, Oct 3, 2017 at 12:52 PM, GALLAGHER, WILLIAM <[login to unmask email]<mailto:[login to unmask email]>> wrote:
Our environment: DB2 z/OS v11

After a couple of months on the bench, I’m back in the game and working a contract position.

We’re lacking in mainframe DB2 administration tools here, so I’ve been tasked with looking for something that can perform schema comparisons between two databases, such as comparing production to one of our test databases, or comparing two test databases to each other. Basically, something that can look for things such as missing or differences in columns, triggers, foreign keys, indexes, etc.

The only mainframe DB2 tool we have is File Manager for DB2, which doesn’t seem to have this functionality from what I can tell. Then again, I’m still trying to learn it, so maybe I haven’t found it yet.

Purchasing something like the BMC or CA tools for DB2 z/OS is not an option due to cost.

So I’m looking for relatively inexpensive but functional desktop options. I believe CA ERWin may be able to do this, but I don’t have a copy of it (yet).

Looking for other options for tools that perform this kind of functionality. DB Artisan from Idera (formerly from Embarcadero) looks promising. Aqua Data Studios from Aquafold may also be an option.

Are there any other tools that I should look at? Or can anybody share their suggestions and/or experiences with the other tools mentioned above?

Thanks.

Bill Gallagher
DB2 Database Administrator
State of Connecticut
[login to unmask email]<mailto:[login to unmask email]>


-----End Original Message-----

Nadir Doctor

DB2 z/OS Schema Compare Tools
(in response to Bill Gallagher)
Hi Bill,

I've used both tools and found each to be useful - perhaps some
functionality available in DB Artisan and related suite of Embarcadero
which is absent in Aqua Data Studio will be bundled in latter to benefit
their larger customer base.


Best Regards,
Nadir


On Tue, Oct 3, 2017 at 2:38 PM, GALLAGHER, WILLIAM <[login to unmask email]>
wrote:

> Nadir,
>
>
>
> Thanks for passing this on. This is interesting. With two products that
> were somewhat competing against each other (DB Artisan and Aqua Data
> Studio), I wonder which one will be chosen by Idera to survive going
> forward?
>
>
>
> Bill Gallagher
>
> DB2 Database Administrator
>
> State of Connecticut
>
> [login to unmask email]
>
>
>
>
>
> *From:* Nadir Doctor [mailto:[login to unmask email]
> *Sent:* Tuesday, October 03, 2017 3:30 PM
> *To:* [login to unmask email]
> *Subject:* [DB2-L] - RE: DB2 z/OS Schema Compare Tools
>
>
>
> Hi Bill,
>
>
>
> Fyi, Idera has recently acquired Aquafold -
>
>
>
> https://www.idera.com/about/news/pressreleases/2017/idera-
> acquires-aquafold-to-expand-database-tools-business
>
>
>
>
> Best Regards,
>
> Nadir
>
>
>
>
>
> On Tue, Oct 3, 2017 at 1:16 PM, Sam Baugh <[login to unmask email]> wrote:
>
> AQT is fairly inexpensive (Advanced Query Tool), and great support. It
> will required having DB2 Connect also.
>
>
>
> On Tue, Oct 3, 2017 at 12:52 PM, GALLAGHER, WILLIAM <[login to unmask email]>
> wrote:
>
> Our environment: DB2 z/OS v11
>
>
>
> After a couple of months on the bench, I’m back in the game and working a
> contract position.
>
>
>
> We’re lacking in mainframe DB2 administration tools here, so I’ve been
> tasked with looking for something that can perform schema comparisons
> between two databases, such as comparing production to one of our test
> databases, or comparing two test databases to each other. Basically,
> something that can look for things such as missing or differences in
> columns, triggers, foreign keys, indexes, etc.
>
> The only mainframe DB2 tool we have is File Manager for DB2, which doesn’t
> seem to have this functionality from what I can tell. Then again, I’m
> still trying to learn it, so maybe I haven’t found it yet.
>
> Purchasing something like the BMC or CA tools for DB2 z/OS is not an
> option due to cost.
>
> So I’m looking for relatively inexpensive but functional desktop options.
> I believe CA ERWin may be able to do this, but I don’t have a copy of it
> (yet).
>
> Looking for other options for tools that perform this kind of
> functionality. DB Artisan from Idera (formerly from Embarcadero) looks
> promising. Aqua Data Studios from Aquafold may also be an option.
>
>
>
> Are there any other tools that I should look at? Or can anybody share
> their suggestions and/or experiences with the other tools mentioned above?
>
>
>
> Thanks.
>
>
>
> Bill Gallagher
>
> DB2 Database Administrator
>
> State of Connecticut
>
> [login to unmask email]
>
>
>
>
> -----End Original Message-----
>
> -----End Original Message-----
>
>

Raymond Bell

DB2 z/OS Schema Compare Tools
(in response to Sam Baugh)
…and it comes from New Zealand too, like all the best things. ;o)

Raymond Bell
DB2 Database Administrator | IT Operations | Technology | RBS

From: Sam Baugh [mailto:[login to unmask email]
Sent: 03 October 2017 19:16
To: [login to unmask email]
Subject: [DB2-L] - RE: DB2 z/OS Schema Compare Tools


*********************************************
" This message originates from outside our organisation. Consider carefully whether you should click on any links, open any attachments or reply. If in doubt, forward to ~ Phishing"
*********************************************

AQT is fairly inexpensive (Advanced Query Tool), and great support. It will required having DB2 Connect also.

On Tue, Oct 3, 2017 at 12:52 PM, GALLAGHER, WILLIAM <[login to unmask email]<mailto:[login to unmask email]>> wrote:
Our environment: DB2 z/OS v11

After a couple of months on the bench, I’m back in the game and working a contract position.

We’re lacking in mainframe DB2 administration tools here, so I’ve been tasked with looking for something that can perform schema comparisons between two databases, such as comparing production to one of our test databases, or comparing two test databases to each other. Basically, something that can look for things such as missing or differences in columns, triggers, foreign keys, indexes, etc.

The only mainframe DB2 tool we have is File Manager for DB2, which doesn’t seem to have this functionality from what I can tell. Then again, I’m still trying to learn it, so maybe I haven’t found it yet.

Purchasing something like the BMC or CA tools for DB2 z/OS is not an option due to cost.

So I’m looking for relatively inexpensive but functional desktop options. I believe CA ERWin may be able to do this, but I don’t have a copy of it (yet).

Looking for other options for tools that perform this kind of functionality. DB Artisan from Idera (formerly from Embarcadero) looks promising. Aqua Data Studios from Aquafold may also be an option.

Are there any other tools that I should look at? Or can anybody share their suggestions and/or experiences with the other tools mentioned above?

Thanks.

Bill Gallagher
DB2 Database Administrator
State of Connecticut
[login to unmask email]<mailto:[login to unmask email]>


-----End Original Message-----
The Royal Bank of Scotland plc. Registered in Scotland No 90312. Registered Office: 36 St Andrew Square, Edinburgh EH2 2YB. The Royal Bank of Scotland is authorised by the Prudential Regulation Authority, and regulated by the Financial Conduct Authority and Prudential Regulation Authority. The Royal Bank of Scotland N.V. is authorised and regulated by the De Nederlandsche Bank and has its seat at Amsterdam, the Netherlands, and is registered in the Commercial Register under number 33002587. Registered Office: Gustav Mahlerlaan 350, Amsterdam, The Netherlands. The Royal Bank of Scotland N.V. and The Royal Bank of Scotland plc are authorised to act as agent for each other in certain jurisdictions.

National Westminster Bank Plc. Registered in England No. 929027. Registered Office: 135 Bishopsgate, London EC2M 3UR. National Westminster Bank Plc is authorised by the Prudential Regulation Authority, and regulated by the Financial Conduct Authority and the Prudential Regulation Authority.

The Royal Bank of Scotland plc and National Westminster Bank Plc are authorised to act as agent for each other.

This e-mail message is confidential and for use by the addressee only. If the message is received by anyone other than the addressee, please return the message to the sender by replying to it and then delete the message from your computer. Internet e-mails are not necessarily secure. The Royal Bank of Scotland plc, The Royal Bank of Scotland N.V., National Westminster Bank Plc or any affiliated entity (“RBS” or “us”) does not accept responsibility for changes made to this message after it was sent. RBS may monitor e-mails for business and operational purposes. By replying to this message you give your consent to the monitoring of your e-mail communications with us.

Whilst all reasonable care has been taken to avoid the transmission of viruses, it is the responsibility of the recipient to ensure that the onward transmission, opening or use of this message and any attachments will not adversely affect its systems or data. No responsibility is accepted by RBS in this regard and the recipient should carry out such virus and other checks as it considers appropriate.

Visit our website at www.rbs.com http://www.rbs.com

Robert Krall

RE: DB2 z/OS Schema Compare Tools
(in response to Bill Gallagher)

Good Morning Bill,

I am pretty new to db2, but have been a big fan of Toad over AQT.  You should check it out.

https://www.quest.com/products/toad-for-ibm-db2/

If you have your objects in the sysproc schema then the compare will not work since Toad can't query the sysproc schema.  Unfortunate for me we have all our procedures in the sysproc schema so I can't compare most objects.

-Robert

Ludovic Janssens

DB2 z/OS Schema Compare Tools
(in response to Robert Krall)
Hi,


A good schema compare is integrated in IBM Data Studio and Infosphere Data Architect.

Alternatively, you could try out the extensions written for UrbanCode or try out Liquibase (Datical, the commercial version of Liquibase claims to support DB2 for z/OS).


Best regards,


Ludovic Janssens
Senior Consultant
ae nv/sa * architects for business & ict * www.ae.be http://www.ae.be



________________________________
From: Robert Krall <[login to unmask email]>
Sent: Wednesday, October 4, 2017 3:01:27 PM
To: [login to unmask email]
Subject: [DB2-L] - RE: DB2 z/OS Schema Compare Tools


Good Morning Bill,

I am pretty new to db2, but have been a big fan of Toad over AQT. You should check it out.

https://www.quest.com/products/toad-for-ibm-db2/

If you have your objects in the sysproc schema then the compare will not work since Toad can't query the sysproc schema. Unfortunate for me we have all our procedures in the sysproc schema so I can't compare most objects.

-Robert

-----End Original Message-----


AE-Disclaimer:

Dit bericht is vertrouwelijk en exclusief bestemd voor de geadresseerde ervan. Behoudens andersluidende expliciete afspraak is publicatie, gebruik of verspreiding ervan niet toegelaten. Gelieve de afzender onmiddellijk te verwittigen in geval van foutieve adressering of ontvangst en verwijder het bericht. Visies en opinies uitgedrukt in dit bericht zijn persoonlijk van de afzender tenzij aangegeven als zijnde van AE of verbonden ondernemingen.

This communication is intended for the addressee only and is privileged and confidential. Any unauthorized disclosure, copying, distribution or use is prohibited. If you have received this communication in error, please notify the sender immediately and destroy the original communication. Views and opinions are those of the sender unless clearly stated as being those of AE NV, its subsidiaries and/or associated companies.

Tommy Petersen

RE: DB2 z/OS Schema Compare Tools
(in response to Bill Gallagher)

How advanced does it need to be?
I run the below 2 queries using DSNTIAUL on the two schema versions, then compare the results in TSO 3.12, which will give the line numbers, or copy them to an excel spreadsheet and compare there.
It will help you find the differences, but not create the needed DDL.

SELECT
CHAR(TBCREATOR),
CHAR(TBNAME),
CHAR(NAME),
DIGITS(COLNO),
CHAR(COLTYPE),
DIGITS(LENGTH),
DIGITS(SCALE),
CHAR(NULLS)
FROM SYSIBM.SYSCOLUMNS
WHERE TBCREATOR = 'XXXXXXX'
ORDER BY TBCREATOR,
TBNAME,
NAME
;

 

SELECT
CHAR(I.TBCREATOR),
CHAR(I.TBNAME),
CHAR(K.IXNAME),
CHAR(K.COLNAME),
DIGITS(K.COLNO),
DIGITS(K.COLSEQ),
CHAR(K.ORDERING),
FROM SYSIBM.SYSKEYS K, SYSIBM.SYSINDEXES I
WHERE I.TBCREATOR = 'XXXXXXX'
AND I.NAME = K.IXNAME
AND I.CREATOR = K.IXCREATOR
ORDER BY I.TBCREATOR,
I.TBNAME,
K.IXNAME,
K.COLNAME
;