Originally posted by: ankushg

Hi All,

We are using an MDQ file to access a DB2 table via a query (not a 'select *' but a 'select column names' query) using WTX 8.1

The tyeptree was generated using the same select query and the map could access data successfully. Recently, we added a few columns to the table for which the table was dropped and created again.

We expected that this would not hamper functioning of the map since the MDQ select uses column names rather than a 'select *'.

However, now the trace shows that the query has been executed but probably the data is not being read by the existing type tree and the map step of the JCL fails.

We created a fresh mdq (after the table got re-created) and created a new typtree. This time the map worked. However, we cannot recreate typetree everytime we add new columns to the table being accessed. Any ideas why this is happening?

Thanks in advance!
#DataExchange
#IBMSterlingTransformationExtender
#IBM-Websphere-Transformation-Extender

Originally posted by: NancyR

Create a query in your .mdb that selects the columns you use. Create a type tree from that query and use it in you input card. That will insulate your map from any table changes.
#IBMSterlingTransformationExtender
#IBM-Websphere-Transformation-Extender
#DataExchange

Originally posted by: ankushg

Hi Nancy, thanks for your response. The query is written in an MDQ file. Can you please explain what an MDB file is?
#DataExchange
#IBMSterlingTransformationExtender
#IBM-Websphere-Transformation-Extender

Originally posted by: SystemAdmin

I've run into the same issue; an altered table invalidates existing trees, even though I used names and not "SELECT *" like you did.

Nancy is using Microsoft Access terms (a "MDB" is an where Microsoft stores it's data, a "query" is their term for a database "view").

As you are using DB2, to insulate table changes from the trees you could create a DB2 "View" (a logical table) and reference the view name in WTX instead. As long as you are making changes to the table with ALTER TABLE (and not dropping and re-creating the table), the view will insulate the changes. Make sure to reference field names in the view, and note you may need to issue grants on the view if it wasn't created by the same user running the maps ( example: GRANT SELECT ON viewname TO USER wtxusername )

Unfortunately, if you are dropping and re-creating that will invalidate the VIEW so this idea doesn't help much... You will see " viewname cannot be used because it has been marked inoperative " errors if a table was dropped.
#IBMSterlingTransformationExtender
#DataExchange
#IBM-Websphere-Transformation-Extender

Originally posted by: ankushg

That is quite helpful.Thanks. Just to bring to your notice:

1.We are using column names and not select *.

2. We are using a view in our select queries in WTX, but while adding columns to the table the table is being dropped and recreated. We will look at making our changes only with ALTER table.

This works fine on windows platform where we connect to DB2 using ODBC. When we deploy our maps on ZOS we face the following problems:

1. The select column names query does not work on ZOS . It seems that the JCL cannot read huge query with column names (there are about 500 column names on the view). The JCL just reads the query partially and stops executing (user abend).

2. We still have to create a fresh type tree from mdq everytime we add columns to any of the tables in the view. May be using ALTER table command instead of DROP and CREATE will help.
#IBM-Websphere-Transformation-Extender
#IBMSterlingTransformationExtender
#DataExchange

Originally posted by: phil2030

It shouldn't matter whether you drop the table or not when altering it - as long as you don't change the properties of existing columns. I take it you have reported this as a bug (or issue) to IBM ... if not, you should do so.

Are you saying, even if you rebuild the tree, you still have problems running it on your desired OS because of the data volume limit imposed within the OS? I would throw this to your IBM rep. and raise as a major issue with IBM immediately if you haven't done so already.

With regard to the query, you are using all 500 column values? Can't you trim it down? 500 columns seems a massive amount of data for one row - I am not even going to ask how many rows. You could "unhook" some of your joins and break it apart, then do the lookups in the map (obviously sort the data so you can perform binary searches). This "should" get around any adapter issues on you platform.

Either way, throw it at IBM first...
#DataExchange
#IBM-Websphere-Transformation-Extender
#IBMSterlingTransformationExtender

Originally posted by: phil2030

Can you explain: "However, now the trace shows that the query has been executed but probably the data is not being read by the existing type tree..."? What is "probably"? It either "is" or "is not".

If it is not, what is the problem indicated in the trace? At a best guess, there is either a bug in the adapter or one of the columns in your result set has changed.
#DataExchange
#IBM-Websphere-Transformation-Extender
#IBMSterlingTransformationExtender

Originally posted by: ankushg

All, thanks for your response. The issue is that the type tree generated using the MDQ file is not accepting the data stored in the table.

All numeric fields are showing presentation errors in the trace.

Example:
A decimal (12,0) field on the DB2 table is being declared as Subclass:Number and Presentation:Integer (not decimal) on the typetree with max size 20.
While reading the this field the data is being read as 123456789012. of length 13. (the decimal point is being read extra). Since the presentation is 'Integer' the map is treating this data as invalid input because of the decimal point.
The most important point to note is that this behaviour is visible only on the ZOS platform. On windows (when connected to DB2 using ODBC) the data is read perfectly. Not sure why the MDQ file is generating a typetree from a table whose data it cannot read on ZOS??!!
#IBMSterlingTransformationExtender
#IBM-Websphere-Transformation-Extender
#DataExchange