The defect with only the first character of the column name being returned is described in IBM APAR SE63700:
The fix should be integrated into the new version of the IBM Access Client Solutions Application Package for Linux, due mid-July ...
I believe this is a bug with the specific version of IBM's driver I was using. I was using the ODBC driver from IBM i Access Client Solutions Linux AP v07.03.00.
I replaced that driver with the ODBC driver from IBM i Access for Linux v7.1.0-1.0 and now everything works as expected.
EXECUTE ... USING requires host variable names, not expressions, while SUBSTRING(whatever) is an expression. You will need to declare four host variables, assign results of the SUBSTRING(...) expressions to them, then use those variables in the EXECUTE statement.
Alternatively, you could move expressions to the dynamic statement itself:
It's a very open-ended question, as there is no sure-fire method to determine the server version and platform. You'll need to apply some heuristics.
In Db2 for LUW (and only LUW) since v 10.1 there's a system view dbmcfg that can return the server version:
select value from sysibmadm.dbmcfg where name = 'release'
so if it succeeds you'll be sure it's Db2 ...
Why are you using a table? The IBM i provides a native data queue object...
If your application is running off platform in a language that doesn't offer direct access to the native objects, you could always build a stored procedure or user defined function on the I to provide access to the data queue.
Having said that, the answer to your question is the ...
Well, I did a lot of searching, found some information on the SQL0204 error, which led me to this support page:
which has instructions on what commands to run on the server if you see that SQL0204 error.
I forwarded that on to the partners IT guy, who tried it (and a few other commands according ...
provides sql to list the members in a table:
SELECT TABLE_PARTITION FROM SYSPARTITIONSTAT
WHERE TABLE_NAME = 'MYTABLE' AND TABLE_SCHEMA = 'MYSCHEMA'
You can then loop through the list & use sql along these lines to obtain access to each member in turn:
The best solution, is to install the DB2 Multisystem product and recreate the table as a local "partitioned table".
Behind the scenes, DB2 for i creates the table as a multi-member PF basically just like you have now.
However, SQL statements referencing the table will auto-magically see all the data. Old RPG applications that use OVRDBF to override to a ...
This was due to a version mismatch between the license file and the ODBC driver. The licence .lic file has ProductVersion=10.5 in text in the body. Using the matching driver version has fixed the issue for me.
with tRMS_CODE as
(SELECT DISTINCT RMS_CODE FROM RMS_Table)
, tRMS_ALT1_Cde as
(SELECT DISTINCT RMS_ALT1_Cde FROM RMS_Table)
SELECT DISTINCT * FROM RMS_Table
SUBSTR(CHILD,6,4) NOT IN (SELECT * FROM tRMS_CODE) AND
SUBSTR(CHILD,6,4) NOT IN (SELECT * FROM tRMS_ALT1_Cde) AND
WITH T (YYYYMMDD, HHMM) AS
(20200304, 1046) -- return a timestamp for April 3, 2020, 10:46 AM.
, (20200304, 946) -- return a timestamp for April 3, 2020, 9:46 AM.
, (20200304, 2359) -- return a timestamp for April 3, 2020, 11:59 PM.
, (20200304, 0) -- return a timestamp for April 3, 2020, 0:00 AM.
You don't mention what DBMS you are referring to, but your suggested unique constraint won't work for several out there, because null is not equal to null
create table t (x int not null, y int, unique(x,y));
insert into t (x) values (1);
insert into t (x) values (1);
Does not violate the constraint
Db2 Developer C ...
A before insert trigger might be an option...
However, a "dates table" aka "calendar table" is much more flexible, regardless of the DBMS you are using.
Basically, it's a pre-generated table of dates from say 1900-01-01 to 2200-12-31. In addition to various date columsn, it would contain various flag columns (weekday, weekend, holiday) and other helpful ...
You may start DB2 table journaling with the QSYS2.QCMDEXC procedure and a corresponding text of the STRJRNPF CL command as an argument. Something like this:
CALL QSYS2.QCMDEXC('STRJRNPF FILE(MYFILELIB/MYFILENAME) JRN(MYLIB/USRJRN) IMAGES(*BOTH) OMTJRNE(*OPNCLO)');
For i-series, at least for 7.1, you can try the RESTRICT clause of DROP COLUMN.
There is also a RESTRICT clause for DROP TABLE.
Specifies that the column cannot be dropped if any views, indexes, triggers, or constraints are dependent on the column. 1
If all the columns referenced in a constraint are dropped in the ...