We just installed iSeries 7 box and migrated a database from i5. The migration was to accommodate a new version of vendor's application. They made some changes to the columns by increasing them from CHAR(2) to CHAR(5) and data in those columns was changed certain way.
When I was developing changes required for the client-side queries to work with the new data, the iSeries DBA opened each table in the 'green screen' and refactored the columns. Those changes immediately propagated to the client-side ODBC queries and everything worked fine.
In production he did not refactor - he took a backup from test environment and copied over to production. The problem arose: the queries would still see the column as CHAR(2) thus clipping data to the first 2 characters (i.e. instead of 12345 we would receive 12).
I tried restarting the IIS app pool and web site, rebooting the client machine, but it did not fix the issue. If I added a fake test record to the table, the query would return correct metadata for CHAR(5) but as soon as I deleted the test record, it would revert to wrong CHAR(2). If I added a fake column to the SELECT, same thing would happen, but as soon as I removed the fake column, the query would revert to CHAR(2) again.
It looks like wrong metadata is stuck on the actual iSeries box.
Surprising is the fact that JDBC JtOpen driver works fine with the same SQL select statement, it is completely unaffected by the way columns were refactored (that was one of the factors why this was so confusing).
Our select is very straightforward: something along the lines of
SELECT A, B, C FROM T1 INNER JOIN T2 ON T2.X = T1.Y WHERE T1.Z = 'abc' i.e. nothing fancy. It is submitted from ASP.NET web application via OleDBConnection using IBMDA400 driver.
As a workaround I used
CAST(B AS CHAR(5)) B and everything works, but we would like to remove the change to avoid potential problems if the columns would be further changed. To me it seems that adding a fake column would be pretty safe, but we want to get to the bottom of it, ideally with no changes to the original SELECTs.
How can we force iSeries to inform ODBC driver of refactoring?
I could only think of 2 methods:
Refactor the tables to CHAR(6), run the query on the web site (refresh the page) and refactor back to CHAR(5).
Appreciate any ideas!
PS: I also appreciate if anyone w/o iSeries experience would abstain from posting about other DBMS or IBM database flavours other than iSeries, as that would be not helpful. Sorry!