DB2 zOS to LUW migration

Arun Kumar

DB2 zOS to LUW migration

Hi All,

We are working on a proposal involving migration of DB2 zOS to LUW and want to understand regarding the below items.

1. Datatype differences between zOS and LUW

2. SQL differences between zOS and LUW

3. Any specific considerations to be made for the migration

4. Leveraging IBM Data movement tool(IDMT) for the migration.

Any thoughts regarding this? 

Thanks,

Arun

Peter Conlin

DB2 zOS to LUW migration
(in response to Arun Kumar)
Hi Arun,

You may want to think about codepages, particularly if z/OS Db2s are using ebcdic (vs unicode) databases & encoding scheme.

Anything referencing catalog tables, "environmental" variables, utility and "health check" routines may not port well.

External stored procedures are another area of concern.

Depending on your environment, you may want to consider migration to SQL stored procedures and unicode on z/OS (the latter may wreak havoc on your recovery and DR.)

A pair of us did something similar (20+ yes ago)+, MVS to Linux, different DBMS, in about a month. Several hiccups at first, but smooth sailing & turnover after. It's the intersection of rocket science and common sense. (Think of the former as your vendor tech claims & the latter as your astronaut piloting the vessel.)

Good luck,
Peter
________________________________________
From: Arun Kumar [[login to unmask email]
Sent: Wednesday, August 21, 2019 11:05 PM
To: [login to unmask email]
Subject: [DB2-L] - DB2 zOS to LUW migration

Hi All,

We are working on a proposal involving migration of DB2 zOS to LUW and want to understand regarding the below items.

1. Datatype differences between zOS and LUW

2. SQL differences between zOS and LUW

3. Any specific considerations to be made for the migration

4. Leveraging IBM Data movement tool(IDMT) for the migration.

Any thoughts regarding this?

Thanks,

Arun

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

Jack Campbell

RE: DB2 zOS to LUW migration
(in response to Arun Kumar)

I assume you are very early in the process as the list is quite short, I'm no longer an expert on LUW as I have been out of it for a few years, but items I can think of:

1) Backup/Recovery and DR considerations differ significantly. What solution(s) will you use for HA and HADR?

2) Logging / log archive management is not automatic like Mainframe

3) Scheduling tool for DBA jobs - a big difference between Mainframe and LUW (Unix/Linux)

4) Mainframe is "shared-all" , LUW is "shared-nothing" , how will this impact you

5) Mainframe 'centralized' , LUW distributed - long distances can add network latency issues

6) DATA migration - EBCDIC to UNICODE is not an exact match

7) zparms versus configuration options (system/DB)

..There are lots more, but here's my starter set

HTH

Jack

Philip Nelson

DB2 zOS to LUW migration
(in response to Jack Campbell)
Jack,

Not everything you have said above is necessarily the case any more. For
example -

1) Log management can be fully automatic if you wish it to be so. Even
more so if you use an enterprise backup solution, particularly TSM.
2) pureScale is basically the same "shared all" architecture as DB2 for
z/OS Data Sharing
3) What you mention about network latency applies equally well to z/OS and
LUW with distributed clients (which are probably the majority of clients on
both platforms nowadays)

However the original question makes me worry about how the person who asked
this is going to handle such a project. It appears he knows nothing about
LUW, and I suspect very little about z/OS either. I've seen too many
situations where this has been a recipe for disaster. You could write a
book on the differences between the two platforms, whether it is in terms
of configuration, administration, SQL functionality or just about any
aspect of the products. Even features which are mostly compatible have
subtle differences that can bite you hard.

Probably the biggest gotcha of them all is likely to be the totally
different locking model between the two platforms. At best you are going
to get failures. Worse is that you won't get failures but will get data
integrity issues.

My advice would be that he should get both experienced LUW and z/OS people
(or even someone skilful in both - there are a few of us about) and work
the whole thing through with them.

Phil

On Wed, 28 Aug 2019 at 21:57, Jack Campbell <[login to unmask email]> wrote:

> I assume you are very early in the process as the list is quite short, I'm
> no longer an expert on LUW as I have been out of it for a few years, but
> items I can think of:
>
> 1) Backup/Recovery and DR considerations differ significantly. What
> solution(s) will you use for HA and HADR?
>
> 2) Logging / log archive management is not automatic like Mainframe
>
> 3) Scheduling tool for DBA jobs - a big difference between Mainframe and
> LUW (Unix/Linux)
>
> 4) Mainframe is "shared-all" , LUW is "shared-nothing" , how will this
> impact you
>
> 5) Mainframe 'centralized' , LUW distributed - long distances can add
> network latency issues
>
> 6) DATA migration - EBCDIC to UNICODE is not an exact match
>
> 7) zparms versus configuration options (system/DB)
>
> ..There are lots more, but here's my starter set
>
> HTH
>
> Jack
>
> -----End Original Message-----
>

Suresh Sane

Db2 LUW SQL Procedure - validating parms - looking for a simple example
(in response to Philip Nelson)
Team,

Looking for a very simple code sample for validating a parameter and returning an error if invalid.

Example: parm1 can be Y or N. If something different, return an error with a specific sqlstate and message.

Ideally with no error handler, but can use it if necessary.

Thx
Suresh

________________________________

Philip Gunning

Db2 LUW SQL Procedure - validating parms - looking for a simple example
(in response to Suresh Sane)
You should be able to find something in the SAMPLES/sqlpl sub-directory, as an aside lots of good samples there. Phil


>
> IBM Champion
>
> IBM Gold Consultant
>
> Certified Information Systems Security Professional(CISSP)
>
> Certification Number 539059
>
> Certified DB2 DBA v10.5
>
> Certified Database Adminstrator, DB2 11.1
>
> IBM DB2 LUW Support Page -- https://www.ibm.com/analytics/us/en/technology/db2/db2-linux-unix-windows.html
>
> Skype: DB2LUW
>
> Twitter: DB2LUW
>
> Direct +1.610.451.5801
>
> IDUG DB2-L Hall of Fame
>
> www.philipkgunning.com
>
> IBM Business Partner
>
>> This e-mail, including any attachments, may be confidential, privileged, or otherwise legally protected. It is intended only for the addressee. If you received this e-mail in error or from someone who was not authorized to send it to you, do not disseminate, copy, or otherwise use this e-mail or its attachments. Please notify the sender immediately by reply e-mail and delete the e-mail from your system
>
Sent from my iPhone

> On Aug 28, 2019, at 5:38 PM, suresh sane <[login to unmask email]> wrote:
>
> Team,
>
> Looking for a very simple code sample for validating a parameter and returning an error if invalid.
>
> Example: parm1 can be Y or N. If something different, return an error with a specific sqlstate and message.
>
> Ideally with no error handler, but can use it if necessary.
>
> Thx
> Suresh
>
>
> Site Links: View post online View mailing list online Start new thread via email Unsubscribe from this mailing list Manage your subscription
>
> This email has been sent to: [login to unmask email]
> Discover the best cloning tool on the market. Try BCV5 & the new BCV5 Masking Tool.
> ESAi also has powerful solutions for Buffer Pool Tuning, Log Analysis, TDM, & more.
> http://www.ESAIGroup.com/idug
>
>
> Use of this email content is governed by the terms of service at:
> http://www.idug.org/p/cm/ld/fid=2
>

Ian Bjorhovde

Db2 LUW SQL Procedure - validating parms - looking for a simple example
(in response to Suresh Sane)
Suresh,

Something like:

create or replace procedure validate(in i_parm varchar(5))
begin
   if (i_parm not in ('Y','N')) then
      signal sqlstate '75001' set message_text = 'Invalid parameter.';
   end if;
end
@


call validate('SANE')@
SQL0438N Application raised error or warning with diagnostic text: "Invalid
parameter.". SQLSTATE=75001



Thanks,


Ian Bjorhovde
IBM Gold Consultant

On Aug 28, 2019, 2:39 PM -0700, suresh sane <[login to unmask email]>, wrote:
> Team,
>
> Looking for a very simple code sample for validating a parameter and returning an error if invalid.
>
> Example:  parm1 can be Y or N.  If something different, return an error with a specific sqlstate and message.
>
> Ideally with no error handler, but can use it if necessary.
>
> Thx
> Suresh
>
>
> Site Links: View post online   View mailing list online   Start new thread via email   Unsubscribe from this mailing list   Manage your subscription
>
> This email has been sent to: [login to unmask email]
> Discover the best cloning tool on the market. Try BCV5 & the new BCV5 Masking Tool.
> ESAi also has powerful solutions for Buffer Pool Tuning, Log Analysis, TDM, & more.
> http://www.ESAIGroup.com/idug
>
> Use of this email content is governed by the terms of service at:
> http://www.idug.org/p/cm/ld/fid=2

Kal Sub

RE: Db2 LUW SQL Procedure - validating parms - looking for a simple example
(in response to Philip Gunning)

You could try this, Suresh.

create procedure TEST (IN IN1 VARCHAR(1))
dynamic result sets 0
not deterministic
language sql
no external action
begin

if IN1 <> 'Y' and IN1 <> 'N'
then
signal sqlstate '12345'
set message_text = 'Invalid Input';
end if;

end @

Regards

Kals
In Reply to Philip Gunning:

You should be able to find something in the SAMPLES/sqlpl sub-directory, as an aside lots of good samples there. Phil


>
> IBM Champion
>
> IBM Gold Consultant
>
> Certified Information Systems Security Professional(CISSP)
>
> Certification Number 539059
>
> Certified DB2 DBA v10.5
>
> Certified Database Adminstrator, DB2 11.1
>
> IBM DB2 LUW Support Page -- https://www.ibm.com/analytics/us/en/technology/db2/db2-linux-unix-windows.html
>
> Skype: DB2LUW
>
> Twitter: DB2LUW
>
> Direct +1.610.451.5801
>
> IDUG DB2-L Hall of Fame
>
> www.philipkgunning.com
>
> IBM Business Partner
>
>> This e-mail, including any attachments, may be confidential, privileged, or otherwise legally protected. It is intended only for the addressee. If you received this e-mail in error or from someone who was not authorized to send it to you, do not disseminate, copy, or otherwise use this e-mail or its attachments. Please notify the sender immediately by reply e-mail and delete the e-mail from your system
>
Sent from my iPhone

> On Aug 28, 2019, at 5:38 PM, suresh sane <[login to unmask email]> wrote:
>
> Team,
>
> Looking for a very simple code sample for validating a parameter and returning an error if invalid.
>
> Example: parm1 can be Y or N. If something different, return an error with a specific sqlstate and message.
>
> Ideally with no error handler, but can use it if necessary.
>
> Thx
> Suresh
>
>
> Site Links: View post online View mailing list online Start new thread via email Unsubscribe from this mailing list Manage your subscription
>
> This email has been sent to: [login to unmask email]
> Discover the best cloning tool on the market. Try BCV5 & the new BCV5 Masking Tool.
> ESAi also has powerful solutions for Buffer Pool Tuning, Log Analysis, TDM, & more.
> http://www.ESAIGroup.com/idug
>
>
> Use of this email content is governed by the terms of service at:
> http://www.idug.org/p/cm/ld/fid=2
>

David Simpson

Db2 LUW SQL Procedure - validating parms - looking for a simple example
(in response to Philip Gunning)
IF PARM1 NOT IN ('Y','N') THEN
SIGNAL SQLSTATE '75001' SET MESSAGE_TEXT = 'PARM1 INVALID';
END IF;

You makeup the SQLSTATE and message. The SQLSTATEs beginning with 7 and above are for user assignment.

From: Philip Gunning <[login to unmask email]>
Sent: Wednesday, August 28, 2019 5:17 PM
To: [login to unmask email]
Subject: [DB2-L] - RE: Db2 LUW SQL Procedure - validating parms - looking for a simple example

You should be able to find something in the SAMPLES/sqlpl sub-directory, as an aside lots of good samples there. Phil


IBM Champion
IBM Gold Consultant
Certified Information Systems Security Professional(CISSP)
Certification Number 539059
Certified DB2 DBA v10.5
Certified Database Adminstrator, DB2 11.1
IBM DB2 LUW Support Page -- https://www.ibm.com/analytics/us/en/technology/db2/db2-linux-unix-windows.html
Skype: DB2LUW
Twitter: DB2LUW
Direct +1.610.451.5801
IDUG DB2-L Hall of Fame
www.philipkgunning.com http://www.philipkgunning.com/AskMrDB2
IBM Business Partner
This e-mail, including any attachments, may be confidential, privileged, or otherwise legally protected. It is intended only for the addressee. If you received this e-mail in error or from someone who was not authorized to send it to you, do not disseminate, copy, or otherwise use this e-mail or its attachments. Please notify the sender immediately by reply e-mail and delete the e-mail from your system
[cid:]
Sent from my iPhone

On Aug 28, 2019, at 5:38 PM, suresh sane <[login to unmask email]<mailto:[login to unmask email]>> wrote:
Team,

Looking for a very simple code sample for validating a parameter and returning an error if invalid.

Example: parm1 can be Y or N. If something different, return an error with a specific sqlstate and message.

Ideally with no error handler, but can use it if necessary.

Thx
Suresh

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

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

Suresh Sane

Db2 LUW SQL Procedure - validating parms - looking for a simple example
(in response to David Simpson)
Thank you David and Ian. I have done this in a trigger and z/OS proc but had not on LUW. Wanted a confirmation before suggesting it. Strangely, google did not turn up much withoutt using a handler.

Sorry for the "newbie" question.

Thx
Suresh

________________________________
From: David Simpson <[login to unmask email]>
Sent: Thursday, August 29, 2019 4:48 PM
To: [login to unmask email] <[login to unmask email]>
Subject: [DB2-L] - RE: Db2 LUW SQL Procedure - validating parms - looking for a simple example


IF PARM1 NOT IN ('Y','N') THEN

SIGNAL SQLSTATE '75001' SET MESSAGE_TEXT = 'PARM1 INVALID';

END IF;



You makeup the SQLSTATE and message. The SQLSTATEs beginning with 7 and above are for user assignment.



From: Philip Gunning <[login to unmask email]>
Sent: Wednesday, August 28, 2019 5:17 PM
To: [login to unmask email]
Subject: [DB2-L] - RE: Db2 LUW SQL Procedure - validating parms - looking for a simple example



You should be able to find something in the SAMPLES/sqlpl sub-directory, as an aside lots of good samples there. Phil





IBM Champion

IBM Gold Consultant

Certified Information Systems Security Professional(CISSP)

Certification Number 539059

Certified DB2 DBA v10.5

Certified Database Adminstrator, DB2 11.1

IBM DB2 LUW Support Page -- https://www.ibm.com/analytics/us/en/technology/db2/db2-linux-unix-windows.html

Skype: DB2LUW

Twitter: DB2LUW

Direct +1.610.451.5801[cid:3634fb8e-1cc0-47ee-8cee-91c965823980]

IDUG DB2-L Hall of Fame

www.philipkgunning.com http://www.philipkgunning.com/AskMrDB2

IBM Business Partner

This e-mail, including any attachments, may be confidential, privileged, or otherwise legally protected. It is intended only for the addressee. If you received this e-mail in error or from someone who was not authorized to send it to you, do not disseminate, copy, or otherwise use this e-mail or its attachments. Please notify the sender immediately by reply e-mail and delete the e-mail from your system

[cid:]

Sent from my iPhone

On Aug 28, 2019, at 5:38 PM, suresh sane <[login to unmask email]<mailto:[login to unmask email]>> wrote:

Team,



Looking for a very simple code sample for validating a parameter and returning an error if invalid.



Example: parm1 can be Y or N. If something different, return an error with a specific sqlstate and message.



Ideally with no error handler, but can use it if necessary.



Thx

Suresh



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



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

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

  • Outlook-3h4oedkx.png (<1k)