My company is porting our software to a new Database (DB2 for iSeries (7.1)) because of customer wishes. Whe have an automatic upgrading system which drops, alters and creates. The iSeries seems to have a very special way to handle warnings - inquiry messages. As example when i drop our lib via SQL (ADO.NET) the jobs halts and waits for user input.
Is filling SYSRPYL and CALL QSYS2.QCMDEXC('CHGJOB INQMSGRPY(*SYSRPYL)') the only way to get those blocking messages out of the way or is there some global switch so that the iSeries behaves like a normal database? If we need to fill this table is there a way to obtain all relevant messages or do we need to wait to see them happen the first time before we can react?
The other problem seems to be syntax problems - we have our project running on MSSQL, Oracle and DB2 LUW - on iSeries things like using a defined with clause in a join (join something on something.col in (select othercol from W_WithClause)) does not seem to work. Is there a way to make the iSeries to comply to this SQLs or do we need to re-test everything and rewrite our SQLs?
You might look at INQMSGRPY(*DFT)...
But you'd have to look at the messages and see if they have a default and what it is. You can use the Display Message Description (DSPMSGD) command to find the defaults for a message.
For instance, dropping a newly created schema results in at the very least a CPA7025 - Receiver QSQJRN0001 in CWTEST never fully saved. (I C)
The default for that message is I-Ignore.
As far as SQL syntax compatibility, I'm afraid your only choice is to rewrite the statement. Db2 for i is not Oracle, nor MS SQL Server, nor even DB2 for LUW. The codebase for DB2 for i is separate from the codebase for DB2 for LUW and DB2 for z/OS for that matter.
If you haven't already found it, the IBM DB2 for i Porting Information page is a good place to start.
On the plus side, since IBM DB2 for i so closely follows the ANSI/ISO SQL Standards, a statement that works on DB2 for i is likely to work on the others DBs. It's the non-standard syntax you current have for other DBs that will give you trouble.
On the downside, v7.1 is over 6 years old. v7.2 was released in 2014 and v7.3 was released this year. So you're going to be missing out on some newer functionality. Some enhancements are ported back to earlier releases. You can take a look at the DB2 for i - Technology Updates page.