Re: SQL on V5R1

On 16-Oct-2015 15:51 -0500, James Rich wrote:

Today I tried to create a table only to discover that the SQL
statement doesn't appear to be supported. I checked the release and
discovered this particular system is at V5R1, much to my dismay.
Upgrading to a new IBM i might be a possibility, but before looking
further into that the customer wants to explore what the other
possibilities are. Here is the statement we're trying to run:

create table qs36f/PDFRPTP
(REPORT FOR RPID bigint generated always as identity
( start with 1, increment by 1, no order, no cycle
, no minvalue, no maxvalue, cache 20),
DATA FOR RPDATA blob(2G),
REPORTDATE FOR RPDATE timestamp,
NAME FOR RPNAME varchar(128),
TYPE FOR RPTYPE char(4),
NEWREPORT FOR RPNEW char(1),
USERNAME FOR RPUSER varchar(128),
DELETED FOR RPDELETED char(1) not null,
ARCHIVED FOR RPARCHIVE char(1) not null,
PRIMARY KEY (RPID))

Running the statement results in:

Token GENERATED was not valid. Valid tokens: NO FOR NOT FILE WITH
CCSID CHECK UNIQUE DEFAULT PRIMARY.

Two options occur to me:

1. Is there a different syntax on V5R1 to accomplish the same thing?

No. There is no IDENTITY column support on that release. The feature was added with V5R2.

The v5r1 syntax shows [while still available online]:
[http://publib.boulder.ibm.com/iseries/v5r1/ic2924/index.htm?info/db2/rbafzmsthctabl.htm]

"...

column-definition:

|--column-name--+-------------------------------------+----->
| .-COLUMN-. |
'-FOR--+--------+--system-column-name-'

>--data-type------------------------------------------------>

.----------------------------------------------------------.
V |
>---+------------------------------------------------------+-+-|
+-NOT NULL-----------------------------------------------+
+-default-clause-----------------------------------------+
| (1) |
+-datalink-options---------------------------------------+
'-+----------------------------+-+-+-UNIQUE------+-----+-'
'-CONSTRAINT-constraint-name-' | '-PRIMARY KEY-' |
+-references-clause---+
'-CHECK-(-check-cond)-'

..."


The v5r2 CREATE TABLE syntax shows [while still available online], conspicuously the addition of "GENERATED ... identity-options":
[http://publib.boulder.ibm.com/iseries/v5r2/ic2924/index.htm?info/db2/rbafzmsthctabl.htm]
"...

column-definition:

|--column-name--+-------------------------------------+------->
| .-COLUMN-. |
'-FOR--+--------+--system-column-name-'

>--data-type-------------------------------------------------->

.-------------------------------------------------------.
V |
>----+---------------------------------------------------+-+--|
+-default-clause------------------------------------+
| .-GENERATED ALWAYS-----. (1) |
+-+----------------------+-----+------------------+-+
| '-GENERATED BY DEFAULT-' '-identity-options-' |
| (2) |
+-datalink-options----------------------------------+
+-NOT NULL------------------------------------------+
'-column-constraint---------------------------------'

Notes:

1. GENERATED can be specified only if the column has a ROWID data type (or a distinct type that is based on a ROWID data type), or the column is an identity column.
..."

2. Is there a PTF for V5R1 that gives the GENERATED capability?
How would I find out? Is it even still available?

I do not recall the support being PTFed to an earlier release. Since for several years already, the v5r1 information about APAR\PTF is no longer visible on the web, thus effectively only articles discussing that the feature was moved back via PTFs would be available. PTFs may still be available for ordering for some obsolete releases [and if so, mostly to allow the desperate to pay big $s for per-incident basis under contracted assisance], but I would think by now v5r1 would be totally cut off.

A potentially viable alternative, given the prior two are unlikely:

3. Although not allowing the syntax to remain the same for the column definition [apparently taken\composed from a higher release on which the coding\testing of the application is apparently being done]:

Creating an adding a *before insert trigger might be the best means to mimic the effect; i.e. as a rule of the file, the trigger maintains that rule, in place of [for lack of] the support by the database to due that auto-increment. I would probably choose a native trigger, although an SQL trigger might be the simplest, especially if there is not much contention and the SQL can be allowed to fail; only because in my perception, I feel the recovery from trying to insert a duplicate is more easily accomplished in RPG than SQL procedural language.