Category Archives: DB2 for i (iseries, as400)

DB2 on IBM i Power CPUs

Netbeans: Creating Stored Procedures or running Dynamic compound statement on IBMi DB2

I finally figured out how you can create a stored procedure or run Dynamic compound statements in Netbean’s Database service on the IBMi.  My problem was that whenever I tried to run a create stored procedure script the SQL editor didn’t know how to parse it correctly so it would send the command line by line to the database using the semi colon as the delimiter instead of as one contiguous command.

The key to getting this to work in Netbeans is to create a Delimiter that marks the end of the SQL that you want to send to the system.  In the example below I send a request that creates 2 temp tables, and then 2 requests to pull the data from those tables.  This example is simple and doesnt show the full power of a DCS.  The dynamic compound statements allow you to use variables, control logic, cursors, etc… to do database specific tasks.  Its like a stored procedure except you don’t have to create it first.   see:

https://www.ibm.com/developerworks/community/wikis/home?lang=en#!/wiki/IBM%20i%20Technology%20Updates/page/Dynamic%20Compound%20statement

and

https://www.ibm.com/support/knowledgecenter/ssw_ibm_i_72/sqlp/rbafydyncompound.htm

It did seem that a dynamic compound statement can’t return a result set for some reason (at least when i tried).

Below is an example of the error that I was getting when i was running into this issue where the database service was not parsing the CREATE PROCEDURE statement as one contiguous chunk.

Error code -104, SQL state 42601: [SQL0104] Token <END-OF-STATEMENT> was not valid. Valid tokens: ;.
Line 2, column 1

Below is me trying DCS in different IBMi tools

cimmfjkweaexyhx

Advertisements

Kill all jobs with a record lock on a specific file via “DB2 for i” SQL

Below is a stored procedure that uses the DB2 for i service QSYS2.RECORD_LOCK_INFO to get all the record locks on a specific file.  It then iterates over all the records and passes the JOB_NAME to ENDJOB to kill the jobs that are creating the lock on the file.  Of course this is very dangerous and should be only used in certain instances where you are sure that the jobs you’ll be killing will not matter.

 

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

Run #DB2 SQL Scripts added to #IBMi Access

The latest version of IBMi Access released today (Dec 9 2015) now allows you to run SQL scripts!  It also has an improved UI with color coded SQL!

RunSQLIBMiAccessClientSolutionsColorCoded

You can download the latest IBM i Access Client Solutions here: https://t.co/jInoj0OtDX

Big thanks to Jesse Gorzinski’s team and everyone at IBM that made this possible!  We finally aren’t married to the Windows iSeries Navigator for running SQL!  We can now use this tool on Mac, Linux or Windows!

 

Run DB2 SQL from the command line in SSH in a Bash shell on IBMi

Aliases in unix are used to make it easier to run commands that are long or hard to remember.  Below is an example of how to create an alias to run DB2 SQL

WARNING! – This is just an experiment and you might want to add some security to this to make sure that sql injection and other things don’t occur.

Shout out to @tweetjbh https://twitter.com/tweetjbh for the ideas!

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

PHP DB2 on IBMi error: MYTABLE in MYFILE not valid for operation. SQLCODE=-7008

UPDATE:  Make sure to check if i5_allow_commit is not 0 (No Commit *NC *NONE).  You can check by running this in your PHP code

var_dump(ini_get(‘ibm_db2.i5_allow_commit’));

If the value is not 0 you might fix the problem by setting it to 0 in /usr/local/zendsvr/etc/conf.d/ibm_db2.ini and doing an Apache restart on Zend Server going to the 5250 typing “GO ZENDSVR/ZSMENU” option 6, option 5.

In case you run into the issue where your SQL updates and inserts statements aren’t processing you may want to try appending “WITH NONE” to the SQL statement.

When I ran the update statement

UPDATE MYFILE.MYTABLE SET FIELD1 = ? WHERE FIELD2 = ?

I got

MYTABLE in MYFILE not valid for operation. SQLCODE=-7008

Its a new file created via SQL
When I added WITH NONE it works

UPDATE MYFILE.MYTABLE SET FIELD1 = ? WHERE FIELD2 = ? WITH NONE

Even hard coding the values created the same error.  The problem appears to be a commitment control / journaling issue with IBMi DB2 connection.  You could try to change the settings of your connection.