DB2 z/OS V8 CM - Implementing Application Sort Tables

Dale Hardy

DB2 z/OS V8 CM - Implementing Application Sort Tables
One of our developers wants me to create a "sort table" for one of their
applications.
They sort table would contain all the primary keys of another table plus
a column to indicate the sort sequence.

The original table is
TABLEA
COL1 COL2
1 A
2 B
3 L
4 D
5 E
6 F

COL1 is the primary key.
The application wants to have the data in this sequence
L
A
B
D
E
F

My 1st solution was:

Select COL2 ,CASE WHEN COL2 = 'L' THEN 0 ELSE 1 END AS SORT
FROM TABLEA
ORDER BY SORT, COL2;

They didn't like this solution because it wasn't "open and flexible."
If at some future date they wanted to revise the sort order, they would
have to alter the SQL.

The actual SQL will be in an separate XML file and executed dynamically,
so my 2nd suggestion was

Select COL2, 1 from TABLEA where COL2 = ?
Union
Select COL2, 2 from TABLEA where COL2 <> ?
Order by 2,1;

Again this rejected because it still wasn't "open and flexible". The
application code would have to be changed to pass a different parameter
value if the sort order had to be changed.

The developer's manager insisted we build a separate sort table as

TABLEB
COL1 SRT
1 2
2 3
3 1
4 4
5 5
6 6

And then the SQL would be

Select COL2
From TABLEA A ,TABLEB B
Where A.COL1=B.COL1
Order by B.SRT;

The manager didn't want to add a sort column to the original table
because it would vioalate the original logical design.

If they desired to change the sort order in the future, it would not
require any change in the application code.
Someone would just have to update the sort table for the new sequence.

All three work scenarios work, that is not my question.
And the tables are small, so there aren't any performance issues.

I am reluctant to build the sort table because
-the problem could be solved with regular SQL
- I don't want to get stuck maintaining the table or be called in if it
has wrong information or missing rows.

My question are
1. has anybody else every implemented a sort table like this? And if
yes, are there any issues to be aware of?
2. Is the "open and flexible" issues real or has my manager been to too
many software conferences?








---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Avram Friedman

Re: DB2 z/OS V8 CM - Implementing Application Sort Tables
(in response to Dale Hardy)
The documented way to do an alternate sort sequence on a DB2 column is to use a Field Procedure Exit Routine.
See
SQL reference for Create Table
or DB2 Admin Guide

Having said this, I have to admit I have no experience with the technique in DB2.

"Hardy, Dale" <[login to unmask email]> wrote:
One of our developers wants me to create a "sort table" for one of their applications.
They sort table would contain all the primary keys of another table plus a column to indicate the sort sequence.
The original table is
TABLEA
COL1 COL2
1 A
2 B
3 L
4 D
5 E
6 F
COL1 is the primary key.
The application wants to have the data in this sequence
L
A
B
D
E
F
My 1st solution was:
Select COL2 ,CASE WHEN COL2 = 'L' THEN 0 ELSE 1 END AS SORT
FROM TABLEA
ORDER BY SORT, COL2;

They didn't like this solution because it wasn't "open and flexible."
If at some future date they wanted to revise the sort order, they would have to alter the SQL.
The actual SQL will be in an separate XML file and executed dynamically, so my 2nd suggestion was
Select COL2, 1 from TABLEA where COL2 = ?
Union
Select COL2, 2 from TABLEA where COL2 <> ?
Order by 2,1;
Again this rejected because it still wasn't "open and flexible". The application code would have to be changed to pass a different parameter value if the sort order had to be changed.
The developer's manager insisted we build a separate sort table as
TABLEB
COL1 SRT
1 2
2 3
3 1
4 4
5 5
6 6
And then the SQL would be
Select COL2
From TABLEA A ,TABLEB B
Where A.COL1=B.COL1
Order by B.SRT;
The manager didn't want to add a sort column to the original table because it would vioalate the original logical design.

If they desired to change the sort order in the future, it would not require any change in the application code.
Someone would just have to update the sort table for the new sequence.
All three work scenarios work, that is not my question.
And the tables are small, so there aren't any performance issues.
I am reluctant to build the sort table because
-the problem could be solved with regular SQL
- I don't want to get stuck maintaining the table or be called in if it has wrong information or missing rows.
My question are
1. has anybody else every implemented a sort table like this? And if yes, are there any issues to be aware of?
2. Is the "open and flexible" issues real or has my manager been to too many software conferences?






--------------------------------------------------------------------------------- Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm



Avram Friedman
(877)311-0480 Voice Mail
[login to unmask email]
Http://www.IBMsysProg.com




---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Dave Nance

Re: DB2 z/OS V8 CM - Implementing Application Sort Tables
(in response to Avram Friedman)
Dale,
Personally, I would not implement this. What expense do you go through using this table updating/maintaining/etc... this table versus at some possible point in the future they may have to change the sort sequence? That would be a 2 minute job, versus the possible issues you mentioned.

Dave Nance

"Hardy, Dale" <[login to unmask email]> wrote:
One of our developers wants me to create a "sort table" for one of their applications.
They sort table would contain all the primary keys of another table plus a column to indicate the sort sequence.
The original table is
TABLEA
COL1 COL2
1 A
2 B
3 L
4 D
5 E
6 F
COL1 is the primary key.
The application wants to have the data in this sequence
L
A
B
D
E
F
My 1st solution was:
Select COL2 ,CASE WHEN COL2 = 'L' THEN 0 ELSE 1 END AS SORT
FROM TABLEA
ORDER BY SORT, COL2;

They didn't like this solution because it wasn't "open and flexible."
If at some future date they wanted to revise the sort order, they would have to alter the SQL.
The actual SQL will be in an separate XML file and executed dynamically, so my 2nd suggestion was
Select COL2, 1 from TABLEA where COL2 = ?
Union
Select COL2, 2 from TABLEA where COL2 <> ?
Order by 2,1;
Again this rejected because it still wasn't "open and flexible". The application code would have to be changed to pass a different parameter value if the sort order had to be changed.
The developer's manager insisted we build a separate sort table as
TABLEB
COL1 SRT
1 2
2 3
3 1
4 4
5 5
6 6
And then the SQL would be
Select COL2
From TABLEA A ,TABLEB B
Where A.COL1=B.COL1
Order by B.SRT;
The manager didn't want to add a sort column to the original table because it would vioalate the original logical design.

If they desired to change the sort order in the future, it would not require any change in the application code.
Someone would just have to update the sort table for the new sequence.
All three work scenarios work, that is not my question.
And the tables are small, so there aren't any performance issues.
I am reluctant to build the sort table because
-the problem could be solved with regular SQL
- I don't want to get stuck maintaining the table or be called in if it has wrong information or missing rows.
My question are
1. has anybody else every implemented a sort table like this? And if yes, are there any issues to be aware of?
2. Is the "open and flexible" issues real or has my manager been to too many software conferences?






--------------------------------------------------------------------------------- Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm



---------------------------------
Yahoo! Photos
Ring in the New Year with Photo Calendars. Add photos, events, holidays, whatever.

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Dale Hardy

Re: DB2 z/OS V8 CM - Implementing Application Sort Tables
(in response to Joel Goldstein)
But wouldn't a field proc substitue a different value of the column?
Only a single application requesting this, other applications wouldn't
want a different sort sequence. The intent was just have a single column
value that is the middle of a normal sort sequence be the first value
returned.

I thought about writing a UDF and then ordering by the UDF'd column.
But, this would probably meet the same resistence because it would
involve maintaining code .

________________________________

From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of Avram Friedman
Sent: Wednesday, January 18, 2006 2:32 PM
To: [login to unmask email]
Subject: Re: [DB2-L] DB2 z/OS V8 CM - Implementing Application Sort
Tables


The documented way to do an alternate sort sequence on a DB2 column is
to use a Field Procedure Exit Routine.
See
SQL reference for Create Table
or DB2 Admin Guide

Having said this, I have to admit I have no experience with the
technique in DB2.

"Hardy, Dale" <[login to unmask email]> wrote:

One of our developers wants me to create a "sort table" for one
of their applications.
They sort table would contain all the primary keys of another
table plus a column to indicate the sort sequence.
The original table is
TABLEA
COL1 COL2
1 A
2 B
3 L
4 D
5 E
6 F
COL1 is the primary key.
The application wants to have the data in this sequence
L
A
B
D
E
F
My 1st solution was:
Select COL2 ,CASE WHEN COL2 = 'L' THEN 0 ELSE 1 END AS SORT
FROM TABLEA
ORDER BY SORT, COL2;

They didn't like this solution because it wasn't "open and
flexible."
If at some future date they wanted to revise the sort order,
they would have to alter the SQL.
The actual SQL will be in an separate XML file and executed
dynamically, so my 2nd suggestion was
Select COL2, 1 from TABLEA where COL2 = ?
Union
Select COL2, 2 from TABLEA where COL2 <> ?
Order by 2,1;
Again this rejected because it still wasn't "open and flexible".
The application code would have to be changed to pass a different
parameter value if the sort order had to be changed.
The developer's manager insisted we build a separate sort table
as
TABLEB
COL1 SRT
1 2
2 3
3 1
4 4
5 5
6 6
And then the SQL would be
Select COL2
From TABLEA A ,TABLEB B
Where A.COL1=B.COL1
Order by B.SRT;
The manager didn't want to add a sort column to the original
table because it would vioalate the original logical design.

If they desired to change the sort order in the future, it would
not require any change in the application code.
Someone would just have to update the sort table for the new
sequence.
All three work scenarios work, that is not my question.
And the tables are small, so there aren't any performance
issues.
I am reluctant to build the sort table because
-the problem could be solved with regular SQL
- I don't want to get stuck maintaining the table or be called
in if it has wrong information or missing rows.
My question are
1. has anybody else every implemented a sort table like this?
And if yes, are there any issues to be aware of?
2. Is the "open and flexible" issues real or has my manager been
to too many software conferences?







------------------------------------------------------------------------
--------- Welcome to the IDUG DB2-L list. To unsubscribe, go to the
archives and home page at http://www.idugdb2-l.org/archives/db2-l.html.
From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at
http://www.idugdb2-l.org. The IDUG List Admins can be reached at
[login to unmask email] Find out the latest on IDUG conferences
at http://conferences.idug.org/index.cfm




Avram Friedman
(877)311-0480 Voice Mail
[login to unmask email]
Http://www.IBMsysProg.com < http://www.ibmsysprog.com/ >


------------------------------------------------------------------------
--------- Welcome to the IDUG DB2-L list. To unsubscribe, go to the
archives and home page at http://www.idugdb2-l.org/archives/db2-l.html.
From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at
http://www.idugdb2-l.org. The IDUG List Admins can be reached at
[login to unmask email] Find out the latest on IDUG conferences
at http://conferences.idug.org/index.cfm

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Joel Goldstein

Re: DB2 z/OS V8 CM - Implementing Application Sort Tables
(in response to Dave Nance)
2. Is the "open and flexible" issues real or has my manager been to too many software conferences?

Your answer is the end part of #2 above - management by airline magazine.

Regards,
Joel



Joel Goldstein
Responsive Systems
Buffer Pool Tool for DB2, the worldwide industry standard

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm