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 ...
They are both the same function but one was specifically designed for IBM PureData System for Analytics, which is the regexp_extract() function. The other function REGEXP_SUBSTR() is for DB2 only and doesn't exist on PureData System for Analytics.
Use the one that correlates to whichever system you're working in (seems like DB2).
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 ...
What platform and version of Db2?
The documentation you linked to is for Db2 for Linux/Unix/Windows (aka LUW).
The db2-midrange tag is for Db2 for IBM i, whose CREATE TABLE documentation shows that only the following columns can be generated..
row change timestamp
Note: The last ...
Db2 versions for different platforms tend to have different features and statement syntax details, so you should always consult documentation for your exact database version and platform. If you do so, you will see that you can only use a limited selection of expressions for generated columns -- either an identity or a row change timestamp.
Instead of 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 ...