Tag Archives: db2fori

ZF2 – Zend_DB Support for number sign # (AKA pound, hash) in a field / column on the IBMi DB2

If you use Zend Framework 2’s Zend\Db\Sql\Select or Zend\Db\Sql\Join “on expression” to create queries for the IBM i DB2 you’ll sometime’s run into the error

“Column or global variable SomeField”# not found. SQLCODE=-206”

This is because the the Adapter/Platform/AbstractPlatform’s quoteIdentifierInFragment function is used to parse the input you provide. It currently doesn’t allow for the pound sign # to be in a column name and it incorrectly puts quotes around the #.  SomeField# becomes “SomeField””#” , i believe.

If you have this problem you can use my change to /Adapter/Platform/IbmDb2.php found here https://github.com/zendframework/zend-db/pull/95/files .  This change creates a quoteIdentifierInFragment function in /Adapter/Platform/IbmDb2.php that overrides the Base Class AbstractPlatform’s quoteIdentifierInFragment function and uses one that supports # by adding # to the regex.

More info on this getting added to the master branch can be found on my pull request here.  Please feel free to add comments, or commit other code that could be helpful:

https://github.com/zendframework/zend-db/pull/95

Insert statements are not effected because columns are quoted differently.  An array of columns is process and passes the entire column element to: $platform->quoteIdentifier($column); https://github.com/zendframework/zend-db/blob/master/src/Sql/Insert.php#L164

The main change is the regex and you can try different cases here: http://sandbox.onlinephpfunctions.com/code/9b50346237ed0de83f2244282081ae5080ad9f75

 

Advertisements

Rename a Column in DB2 for IBM i

Below is some SQL that can be used to rename a column by dropping the column and adding the new column name.  Do realize that this will make you lose the data in the OLDCOLUMNNAME field so you’ll want to create a backup and re-populate the new field using the backup.

Note: If you get Error code -952, SQL state 57014: [SQL0952] Processing of the SQL statement ended. Reason code 10. Its because the ALTER TABLE will result in data loss. The database is trying to warn you and wants you to confirm deletion but jdbc can’t handle the prompt. I’m unsure if there is a JDBC option to always say yes to the prompt. You may have to run this in a 5250 session with STRSQL

Alternatively you could also use the command below
CHGPF FILE(MYLIB/MYTABLE) SRCFILE(QDDSSRC)

You would just need to create the DDS in the source file member MYTABLE that changes the column

Wow! look at whats coming for #IBMi TR11 TR3 #db2 #sql

IBMi Software Announcement: Complete info from IBM

  1. IBM i Access Client Solutions has “Run SQL” interface
    • “Run SQL” scripts was previously only available in the Windows Client (System i Navigator / System i Access for Windows).
  2. Web Sockets w/ Apache level 2.4.12
    • allows for real-time communication.  A lot of chat applications, and real time collaboration apps are using Web Sockets because of the server’s connection to the client.
    • Learn more here
  3. Free-form RPG is no longer forced to columns 8 – 80.
  4. Zend Server for IBM i 8.5 (5639-ZC1)

DB2 for I new features and enhancements

    • ASSOCIATE LOCATOR for processing result sets from a remote db.
    • LOCATE_IN_STRING() – SQL Function –  Find a string inside a string. (PHP devs think strpos)
    • OVERLAY() – SQL Function – easily insert a string/variable inside a string (PHP devs think sprintf but not exactly)
    • Viewing Record locks or Object Locks in SQL by utilizing the DB2 for i services

Other New features:

  1. Open Source tools now included
    1. GCC, Git, .zip, .tar, bash, Python 2.7, and many others in Open Source for IBM i (5733-OPS)
  2. Integrated Web Services (IWS) server makes it easy to open your ILE objects up to webservices done in SOAP or REST

References:

DB2 for i services

System Lock

Record Lock

How to get a table’s primary key(s) from IBMi DB2 via SQL

How to get the primary key(s) of an IBMi DB2 table via SQL.
Below is some example code on how to pull back the primary keys of a table via SQL. This is useful for when your application needs to update or a delete a unique record that your currently working with (be it in PHP, or another language). Below is the SQL code to retrieve MYTABLE primary keys. To learn more about adding a unique field to an existing table – click here.

Note: Primary keys can be multiple columns.  Primary keys require uniqueness so the database will not allow duplicate values in the Primary Key(s).

Add/Change Primary Key of Table

On an existing table you can add or change the primary key via
ALTER TABLE MYLIB.MYTABLE ADD PRIMARY KEY (ID) ;
ALTER TABLE MYLIB.MYTABLE DROP PRIMARY KEY  ADD PRIMARY KEY (ID) ;

Find Primary Key of a Keyed Physical File (aka Table in sql)

You may run into  a Keyed physical file.  To find out what keys that file is using you’ll have to run the following command on the 5250 or CL
DSPFD FILE(MYLIB/MYTABLE)
Page Down until you get to Access Path Description and look for the Number of key fields and Key field (s).  Or if you have System I Navigator.  Get to the table -> right click for context menu -> Show Indexes.  In Indexes for LIB.TABLE click F12, and add Key Columns to the view.

I’ve attempted to create a UDTF to get a keyed physical via SQL.  This might need a little work:

You ‘ll typically run into this issue if you don’t realized the file is already keyed

SQL State: 23515
Vendor Code: -603
Message: [SQL0603] Unique index cannot be created because of duplicate keys. Cause . . . . . :   An attempt was made to create unique index Q_LIBRARY_TABLE_PKEY1_00001 in LIBRARY or add unique constraint Q_LIBRARY_TABLE_PKEY1_00001 in LIBRARY. The operation cannot be performed because the rows in table TABLE in LIBRARY contain one or more duplicate values in the columns used to create the index. Recovery  . . . :   Do one of the following and try the request again: — Remove the UNIQUE attribute from the CREATE INDEX statement. — Remove the UNIQUE constraint from the ALTER TABLE statement. — Change the data in the related table so that all key values are unique. — Specify UNIQUE WHERE NOT NULL on the CREATE INDEX statement if the duplicate keys contain nulls. The uniqueness restriction would not apply when the key value contains nulls. For information on what rows contain the duplicate key values, see the previously listed messages in the job log.

or

SQL State: 42830
Vendor Code: -538
Message: [SQL0538] The FOREIGN key in constraint Q_LIBRARY_TABLE_KEY1_00001 in LIBRARY not same as the parent key. Cause . . . . . :   The FOREIGN key in constraint Q_LIBRARY_TABLE_KEY1_00001 in LIBRARY is not the same as the parent key of table *N in *N. The FOREIGN key must have the same number of columns as the parent key and the data type, attributes, and field procedure of the FOREIGN key must be identical to the data type, attributes, and field procedure of the corresponding column of the parent key. Recovery  . . . :   Correct the statement so that the description of the FOREIGN key conforms to that of the parent key of the specified table.

Thanks to Scott Forstie for the help w/ finding the primary key

DB2 SQL – Select query to get somewhat unique record numbers

Many old tables cannot be changed because compiled RPG programs and display files rely on that table.  To change the table would require re-compiling the programs after modifying the programs to account for the changes to the table.  That rules out altering the table and creating a ROWID or IDENTITY field.

I tried to use the SQL statement: “SELECT row_number() over() FROM MY_TABLE” to get the row number, but that only returns a relative number and not the actual record id as you would see in the green screen.

Therefore to get the unique identifier for the row you can use RRN(MY_TABLE) (Relative Record Number Sequence) to get the record id.  This will only work if the table you are using won’t be reorg’ed.  This is how you would get the record id:

SELECT RRN(MY_TABLE) AS CUSTOMER_ROWID, MY_TABLE.*
FROM MY_TABLE

When you needed to update or delete that record you would do the following

UPDATE MY_TABLE
SET FIELD1='35'
WHERE RRN(MY_TABLE)=2;

Which would update the row with row id 2

Altering the Table to create a unique column

If you’re able to alter the table  you could add a key field with the identity column attribute, which will automatically generate the value/key. Remember that this requires you to re-compile ALL compiled programs (RPG , etc..) that use this table possibly. The value will be generated even if your using non-SQL interface like RPG,CL. You get to set the column name, data type (example below BIGINT), what number to start the count at (START WITH 1) and how much to increment the number (INCREMENT BY 1). Cycle means it will restart at 1 after it is at the highest value capable for BIGINT. Generate Always means the DB always generates the value, you could use Generate By Default, and it will only generate if there wasn’t a value given for the identity column.

ALTER TABLE CUSTOMER
    ADD COLUMN ID BIGINT NOT NULL GENERATED ALWAYS AS IDENTITY
    (START WITH 1 INCREMENT BY 1 CYCLE);
SELECT ID,CUSTOMER.* FROM CUSTOMER -- See the ID col next to all the rows
--Inserting rows into a table with identity column
INSERT INTO CUSTOMER (NAME) VALUE('BOB');
INSERT INTO CUSTOMER VALUE(DEFAULT,'BOB');
--If you don't want the system to generate the values when your pulling from another table you can use OVERRIDING SYSTEM VALUE
INSERT INTO CUSTOMER OVERRIDING SYSTEM VALUE
   (SELECT * FROM THIS_WEEK_CUSTOMERS)
-- Or you can override and do it per insert statement
INSERT INTO CUSTOMER VALUE(32,'BOB') OVERRIDING SYSTEM VALUE;

You can also use a sequence object to re-sequence a preexisting ID field. Just make sure you ensure referential integrity.

CREATE SEQUENCE MYLIB.SEQNUM AS DEC(7, 0);
UPDATE MYLIB.MYTABLE SET KEYFIELD1= DIGITS(NEXT VALUE FOR MYSEQ);
DROP SEQUENCE MYLIB.SEQNUM;

--or

SELECT NEXT VALUE FOR MYLIB.SEQNUM AS NEXT_SEQ_NUM FROM sysibm.sysdummy1
--Use the NEXT_SEQ_NUM value

Set a Primary Key constraint to ensure uniqueness

The code below will add a primary key constraint on the column “ID”.  This will force uniqueness on that column and if an INSERT or UPDATE statements will fail if they try to add a non-unique id. The identity attribute only generates values but doesn’t enforce uniqueness.

ALTER TABLE MYLIB.MYTABLE ADD PRIMARY KEY (ID)

 

Note: the SQL function row_number() with over() can only be used in SELECT statements because of OLAP which makes it kind of worthless…

SELECT ROWID,KEYFIELD1,FIELD1
FROM (SELECT row_number() over (order by KEYFIELD1) as ROWID, KEYFIELD1, FIELD1
      FROM MY_TABLE) as Table1

An OLAP specification is not valid in a WHERE, VALUES, GROUP BY, HAVING, or SET clause, or join-condition in an ON clause of a joined table. An OLAP specification cannot be used as an argument of an aggregate function in the select-list.

I wish the DB2 UDB had an easier way of retrieving a unique record ID in sql.

Get Last Identity Id

IDENTITY_VALUE_LOCAL() – will return the last id generated by the identity column in a job

--Get the last id generated for this job
VALUES IDENTITY_VALUE_LOCAL() INTO :MYVARIABLE
-- Insert multiple records and get multiple ids back
SELECT ID FROM FINAL TABLE(/*MULTIPLE INSERT STATEMENTS*/)

DB2 Get next identity value

-- Get a good guess of what the next value will be:
SELECT TABLE_SCHEMA, TABLE_NAME, NEXT_IDENTITY_VALUE
FROM QSYS2.SYSPARTITIONSTAT
WHERE TABLE_SCHEMA = 'CUSTOMER';

Alternative Solution: UUID AKA GUID

A universally unique identifier (UUID) is a 128-bit number that is randomly generated so that it is nearly impossible to have a duplicate created with even hundreds of trillions of records your chance of a duplicate is 1 in a billion chance of a duplicate – https://en.wikipedia.org/wiki/Universally_unique_identifier In this technique the application can create the UUID and then save it to the database and not rely on the database to generate the unique identifier. For LARGE datasets that you need to sort or compare guids (used in a where clause, or a join, or an db index etc….) there will be a performance hit due to the randomness of the data and the value being a 128 bit value instead of a 32 bit value if you were to use an integer. Its easier to replicate a row that has a UUID since your not bound to the database handing out IDs. Another advantage is that the UUID is so unique that it could be used across multiple applications. One must weigh the pros and cons of both approaches and analyze the system they are building to see what is the best fit for their solution.

-- Create a table with a GUID column with a BINARY(16) datatype.  16 bytes * 8 = 128 Bits
CREATE OR REPLACE TABLE 
MYLIB.MYTABLE (GUID BINARY(16));

-- Create a row
INSERT INTO  MYLIB.MYTABLE 
(GUID) 
VALUES 
(bx'b81583b13af0431c80fe97e07c5ecb42');

--Select all rows
SELECT * 
FROM MYLIB.MYTABLE;

GUID
-----------------------------------
x'B81583B13AF0431C80FE97E07C5ECB42'

  1 RECORD(S) SELECTED.