Things to consider when moving to DB2

[tommy]

By the end of this year we will move from SC3SP3 on OS/390 to SC5.0.1 on Sun Solaris.

About 3-4 months later we want to start using DB2. We have not yet decided if we will shadow or push files.

Main reasons for doing this is:

1: Better performance for reporting
2: To have realtime data available for our customer web

If You have any tips regarding the use of DB2, which files to move or shadow please them here.

[fjespersen]

Hi Tommy,

There used to be a "Peregrine" word document describing all tables and what they are used for, I think this document also included recomendations of which tables to move to RDBMS (if you dont want to move all) to ensure data itegrity.

I have searched all my HD's but I cant find it - Try support, they should have it.

Finn

[sjensen]

Tommy, hopefully you are aiming for DB2 Universial 7.1 on Sun Solaris and NOT on MVS. Mr. Jespersen is right, there is a document and I will try to dig it out for you and upload it.

On the other hand, the "BIG MAN" when it comes to SQL Conversions has spoken. I have discussed this with Karim Teggeur from Peregrine Level 2 and his statement is clear:

a) identify the tables (files) you want to report against and move them with the option called "multi row array table"

b) move all the rest of the files with the option called "default in main table"

I have seen installations running in a mixed environment, meaning that they have files in P4 and files in RDBMS (DB2U, Oracle, SQL), I just wonder what the reason for that is? This must mean that some of the files must have special characteristics, I think this is decsribed in the document that Finn is referring to.

Hope it helps - have fun :lol:

/"Young" Jensen

[fjespersen]

There are a couple of other things to worry about when you move with blob

1. You will not be able to access this data from any other program exept as a blob text.

2. If you allow a search on any of these blob fields (ex. a search on current.pending.groups in cm3r) it will result in a full table scan where each blob are "unpacked" in SC, and you cant index them.

My advice would be to move all table with option "multirow array table" and then identify the few tables/fields you want to pack as blob.

You can check the "sqlhints" table in SC - You used to be able to force move type in this table, but I havent seen it documented anywhere.

Good luck.

[tommy]

Thank You both.

What are your opinion about the recordsize?

I am thinking about increasing the default recordsize when we are still on P4. But how will this impact the move to DB2?

[tommy]

You can check the "sqlhints" table in SC - You used to be able to force move type in this table, but I havent seen it documented anywhere.

Good luck.

We have just started our project to shadow files for reporting purposes and am looking at all the tips.

The sqlhint table. Is this table used during the conversion? Meaning if I find a field in probsummary that should be defined in a certain way can I then define it in here ? Or is this table just for informational purposes?

This is on SC5.

[fjespersen]

Hi Tommy,

As far as I remeber, it works like this with the sqlhints table:

When you move tables to SQL (one at the time or all) you choose a mapping strategy ex: array in separate table.

Each time a table should be moved, SC looks in sqlhints table and check if fields has a "special" setting - ex: data field in SYSBLOB is always moved as blob, regardless of the moving strategy.

I guess this also works for shaddow.

It should be quite easy to test, and I will try one of these days - Please let me know if you get more solid info on the subject.

Finn

[kentyrr]

Tommy,

I'm not really strong on the DBA side of our implementation, but we're using SC4.0.7/Win2K with UDB2/AIX, and I have a few things to say...

First off, and this one is probably 'obvious'... DB2 uses different wild card characters than P4 (and, I presume, this is true of other database products as well), so if you have any queries, conditions, DVD etc written that use wildcards, they'll need to be updated to the correct wildcards for the database system being put into place.

For our reporting, we are using functionality within DB2 to replicate only the tables we need for reporting into a seperate namespace (on a different database server than production servicecenter's database resides on), rather than using ServiceCenter functionality to shadow.

We have almost all files pushed out to UDB2, the better for disaster recovery situations. The one system file we pulled back into P4 after running for a time with it in DB2 is the links table. We found that after modifying a link record, even in the slightest way, while it was pushed out to DB2, SC suddenly couldn't locate the record anymore and we'd have to stop/start all services and logoff/on all users to get back the functionality provided by that link record. When it's in P4, we don't have that problem.

I'm not sure if this answered any of your questions, but I hope it helps nonetheless.

[dx111ge]

this is one way , but if you try to use a cluster , it make sense to push tables like problem and so on to a rdbms and hold all system tables in p4 . Than you can make your changes (new releases ) in fc / links and so on in the second cluster system and without any big downtimes switch over ....

[ambarnes]

Tommy:

I'm a little more curious as to the reasons you posted for move to external RDBMS.

You stated #1 as being better reporting. What do you use for reporting now?

#2 as being better realtime data available for customers. Again, what do you use for that currently?

I only ask as you will recall a similar discussion in another thread on our decision to move back to P4.

[tommy]

We wil shadow for the sole purpose of reporting yes.

Currently we do not allow anybody except outself to run reports with Crystal Reports. We currently use Seagate Infocenter to run and distribute all our reports.

We run special reports that feed a website for our customers, those reports take a long time to process so the data can be delayed as much as 30 minutes and that is not good enough for our customers regarding priority 1 and 2 tickets.

We will select approx 30 files to shadow. Those files will be shadowed on a DB2 on the same server as sc. Then we will replicate those tables to an archive DB2 on a different server.

Our customer web will then display live data directly from the shadowed files.

Reporting will run against either the live shadow or the archive depending on which report it is.

[ambarnes]

I don't know if I'm thinking along the same lines or not here, but if your reports are taking a long time to process and you want to speed it up, would not a faster machine improve the situation also?

[tommy]

No. The reports is also rather complex and several things are processed on batch mode so that would not make it as fast as we want it.