Monthly Archives: November 2014

IBM i DB2 – Synchronizing local database table with Remote table

Setup remote db in Green Screen:

-- Use ADDSVRAUTE so that the system knows the username and password
-- when connecting to the remote system
ADDSVRAUTE USRPRF(MYUSRPRF) SERVER(PRODSERVER) PASSWORD()
-- Or if you already have an entry you may need to change your entry with
CHGSVRAUTE USRPRF(MYUSRPRF) SERVER(PRODSERVER) PASSWORD()
-- Use wrkrdbdire to create/edit remote db connection configurations
WRKRDBDIRE
-- Use CHGDDMTCPA to setup how TCP connection will work (enforcing
-- password policy and encryption)
CHGDDMTCPA AUTOSTART(*YES) PWDRQD(*USRENCPWD) ENCALG(*DES)

alternatively you could create the connection every time doing:


CONNECT TO REMOTE_SYSTEM_NAME USER USERPRF_NAME USING 'MY_PASSWD_IN_SINGLE_QUOTES';

SQL

-- ::Local IBMi::

-- Let's back up the table before we wipe and replace it
CREATE SCHEMA BACKUP

--Create backup
CREATE TABLE BACKUP.TABLETOSYNC
AS (SELECT * FROM MYLIB.TABLETOSYNC)
WITH DATA;

--Wipe local table
DELETE FROM MYLIB.TABLETOSYNC;

--Replace with Production data
INSERT INTO MYLIB.TABLETOSYNC (
SELECT * FROM PRODSERVER.MYLIB.TABLETOSYNC);

--View the table data
SELECT * FROM MYLIB.TABLETOSYNC;

--::MORE ADVANCED WAY WITH MERGE::
--::This would be more elegant but time consuming by setting up MERGE with update insert rules
--instead of wiping and replacing. More advanced example here:http://www.itjungle.com/fhg/fhg092210-story02.html

--Create temp table from production data
CREATE TABLE QTEMP.TABLETOSYNC
AS (SELECT * FROM PRODSERVER.MYLIB.TABLETOSYNC)
WITH DATA;

MERGE INTO MYLIB.TABLETOSYNC AS TARGET_TABLE
USING QTEMP.TABLETOSYNC AS SOURCE_TABLE
ON (TARGET_TABLE.TABLEKEYFIELD=SOURCE_TABLE.TABLEKEYFIELD )
WHEN MATCHED THEN
UPDATE SET
TARGET_TABLE.COL1 = SOURCE_TABLE.COL1
WHEN NOT MATCHED THEN
INSERT (COL1)
VALUES(SOURCE_TABLE.COL1 )
ELSE IGNORE;

-- More info:
-- https://publib.boulder.ibm.com/iseries/v5r1/ic2924/index.htm?info/ddp/rbal1mst63.htm
-- http://www.itjungle.com/fhg/fhg072512-story02.html

-- Another thing i looked into using
-- The connect to command actually puts you into that server instead of just giving you a reference to it....
-- CONNECT TO PRODSERVER USER MYUSRPRF USING 'password';

UPDATE: I’ve put all this logic into a stored procedure and you can download the code here: https://github.com/phpdave/DB2-for-IBMi/blob/master/Stored%20Procedures/SYNCTABLE.sql Let me know if you have any comments for improvement. The current stored procedure has commitment control to save you from sql exceptions or something going wrong in the middle of execution. If a problem occurs everything will be rolled back to a save point (Imagine deleting the database and then it not inserting.)

This might also be helpful and simpler-  https://www.ibm.com/developerworks/community/wikis/home?lang=en#/wiki/IBM%20i%20Technology%20Updates/page/INSERT%20with%20remote%20SUBSELECT

iSeries Navigator

You can also just go to the table in iSeries navigator MS windows client and right click the table and click copy. Then go to the target system and right click on the library you want to paste the table in. This is great for quick manual copies.

PHP: odbc_prepare weird issue with bind parameter on Linux to IBMi DB2 connection

I came across this weird issue using odbc_prepare to prepare a SQL statement with ? in place for the parameters. The query would odbc_prepare “successfully” and the odbc_execute would return “true”, but when i tried to fetch the row nothing came back. I eventually figured out that because my parameter was only 5 characters instead of 6 characters that was the reason the query wasn’t pulling back the records I was expecting.

PHP Code

$queryToPrepare = "SELECT * FROM MYLIB.TABLE WHERE DATE >= ?"
array_push($queryParameters,  str_pad($date, 6, " ", STR_PAD_LEFT));
$preparedQuery = odbc_prepare($this->connection_id, $queryToPrepare);
$success = odbc_execute($preparedQuery, $parameters);
if (odbc_fetch_row($result))
{
echo odbc_result($result, "MYFIELD");
}

DB2 PHP Security Exploit – Older versions of Zend Server for IBM i – Login under any user profile without a password

If you’re running an old version of Zend Server on the IBM i make sure you look at this article from Zend Support:

https://support.zend.com/hc/en-us/articles/203733853-db2-connect-may-allow-blank-password-with-user-entered

Basically you need to rename
/usr/local/zendsvr/lib/libdb400.a to /usr/local/zendsvr/lib/libdb400.a.bak

To do this Rod Flohr suggest you open a PASE shell and issue a mv command to rename the file.

5250 Terminal:

call qp2term

PASE:

mv /usr/local/zendsvr/lib/libdb400.a /usr/local/zendsvr/lib/libdb400.a.bak

If you pass in user supplied parameters to db2_connect function a person could log in as someone else with greater authorities possibly and go into pages or access data they aren’t authorized to use.