view to convert data types

tom

view to convert data types
We are a DB2 Version 8 z/OS shop.

We are working with a vendor whose ‘shrink wrap client’ can handle integer
and character data only.
We are being asked to create a view which converts our small integer,
decimal, date, timestamp, varchar, etc. columns to integer or character.

We feel this would be inefficient and prone to performance problems.
It has always been our standard to have the application handle any
necessary data conversion.

Can anyone comment as to industry standard, best practices, etc?

thanks
tom

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.idug.org/db2-content/index.html has THOUSANDS of free technical presentations!
DB2 LUW, DB2 z/OS, Performance, Installation, Tuning, Coding, BI, Warehouses, - among
many more categories of help waiting for you!
Whether you are an old hand or a DB2 newbie, we have presentations for every level.
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's DB2-L

Philip Sevetson

Re: view to convert data types
(in response to tom)
Tom,

I believe the industry standard in such a case would be to get a different vendor.

Barring that, if this is a business application software package that you're referring to (as opposed to, say, a Telnet "client" for accessing the mainframe), probably what you need to do is set up application-compatible versions of the tables they need and do regular ETL (Extract/Transformation/Load) to populate them and keep them in synchronization with the rest of the company.

--Phil Sevetson

-----Original Message-----
From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of tom
Sent: Thursday, January 14, 2010 1:25 PM
To: [login to unmask email]
Subject: [DB2-L] view to convert data types

We are a DB2 Version 8 z/OS shop.

We are working with a vendor whose 'shrink wrap client' can handle integer
and character data only.
We are being asked to create a view which converts our small integer,
decimal, date, timestamp, varchar, etc. columns to integer or character.

We feel this would be inefficient and prone to performance problems.
It has always been our standard to have the application handle any
necessary data conversion.

Can anyone comment as to industry standard, best practices, etc?

thanks
tom

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.idug.org/db2-content/index.html has THOUSANDS of free technical presentations!
DB2 LUW, DB2 z/OS, Performance, Installation, Tuning, Coding, BI, Warehouses, - among
many more categories of help waiting for you!
Whether you are an old hand or a DB2 newbie, we have presentations for every level.
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's DB2-L

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.idug.org/db2-content/index.html has THOUSANDS of free technical presentations!
DB2 LUW, DB2 z/OS, Performance, Installation, Tuning, Coding, BI, Warehouses, - among
many more categories of help waiting for you!
Whether you are an old hand or a DB2 newbie, we have presentations for every level.
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's DB2-L

Patrick Bossman

Re: view to convert data types
(in response to Philip Sevetson)
Hi Tom,
I haven't performed a study of what would happen in all the different
scenarios. There is a cost to data conversion - but that would not be my
biggest worry.

I think you're right that it's prone to performance problems - especially if
you plan to join one view to another view. I would also be concerned about
predicates against columns from the view that were casted in the select list
of the view. Does the predicate get pushed down and applied against the
base table? Or does the cast preclude that and so you can't get matching
index access against the base table? Do the data type conversions force
more view materialization?

Best regards,
Pat

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.idug.org/db2-content/index.html has THOUSANDS of free technical presentations!
DB2 LUW, DB2 z/OS, Performance, Installation, Tuning, Coding, BI, Warehouses, - among
many more categories of help waiting for you!
Whether you are an old hand or a DB2 newbie, we have presentations for every level.
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's DB2-L

David Seibert

Re: view to convert data types
(in response to Patrick Bossman)
I suggest you put it and some extra shrink wrap in the box and send it back -- unless of course the vendor is one whose name starts with a C and is headquartered in downtown Detroit.

Dave

The contents of this e-mail are intended for the named addressee only. It contains information that may be confidential. Unless you are the named addressee or an authorized designee, you may not copy or use it, or disclose it to anyone else. If you received it in error please notify us immediately and then destroy it.

From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of tom
Sent: Thursday, January 14, 2010 1:25 PM
To: [login to unmask email]
Subject: [DB2-L] view to convert data types

We are a DB2 Version 8 z/OS shop.

We are working with a vendor whose ‘shrink wrap client’ can handle integer
and character data only.
We are being asked to create a view which converts our small integer,
decimal, date, timestamp, varchar, etc. columns to integer or character.

We feel this would be inefficient and prone to performance problems.
It has always been our standard to have the application handle any
necessary data conversion.

Can anyone comment as to industry standard, best practices, etc?

thanks
tom

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.idug.org/db2-content/index.html has THOUSANDS of free technical presentations!
DB2 LUW, DB2 z/OS, Performance, Installation, Tuning, Coding, BI, Warehouses, - among
many more categories of help waiting for you!
Whether you are an old hand or a DB2 newbie, we have presentations for every level.
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's DB2-L

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.idug.org/solutions-journal.html - home of the IDUG Solutions Journal
Technical atricles from world famous authors in DB2's most prestigious, peer reviewed
magazine now on-line!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's DB2-L

David Seibert

Re: view to convert data types
(in response to David Seibert)
Just in case you would appreciate a more lengthy reply...:

Among the questions that occur to me:
1. how many tables do you need to support with this client?
Are you willing to undertake the costs and time required to develop, test, and maintain these views?
2. what do they suggest you have your view do about the decimal scale amounts lost in converting decimal to integer?
3. what do they suggest you do about null values in your data?
4. does this vendor claim to support DB2?
Do they understand that DB2 is a relational database, not just an old British car?
Can they provide reference clients you can consult to confirm their satisfaction with the DB2 support and performance?
5. are any of your keys or index columns non-char or non-integer data?
6. if they only support 2 datatypes, what other DB2 facilities do they not support or exploit?
7. is this software free?

I suggest you have every reason to be concerned about performance. If the answer to 5. above is YES. I would expect a view that requires conversion of those columns would prohibit the use of an index to satisfy queries using those tables. If your company is serious about pursuing a product such as this, perform some testing to compare access times with representative tables and views that would prototype this access.

Dave


The contents of this e-mail are intended for the named addressee only. It contains information that may be confidential. Unless you are the named addressee or an authorized designee, you may not copy or use it, or disclose it to anyone else. If you received it in error please notify us immediately and then destroy it.

From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of tom
Sent: Thursday, January 14, 2010 1:25 PM
To: [login to unmask email]
Subject: [DB2-L] view to convert data types

We are a DB2 Version 8 z/OS shop.

We are working with a vendor whose ‘shrink wrap client’ can handle integer
and character data only.
We are being asked to create a view which converts our small integer,
decimal, date, timestamp, varchar, etc. columns to integer or character.

We feel this would be inefficient and prone to performance problems.
It has always been our standard to have the application handle any
necessary data conversion.

Can anyone comment as to industry standard, best practices, etc?

thanks
tom

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.idug.org/db2-content/index.html has THOUSANDS of free technical presentations!
DB2 LUW, DB2 z/OS, Performance, Installation, Tuning, Coding, BI, Warehouses, - among
many more categories of help waiting for you!
Whether you are an old hand or a DB2 newbie, we have presentations for every level.
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's DB2-L

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.idug.org/solutions-journal.html - home of the IDUG Solutions Journal
Technical atricles from world famous authors in DB2's most prestigious, peer reviewed
magazine now on-line!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's DB2-L