-- 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
-- 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';
-- ::Local IBMi::
-- Let's back up the table before we wipe and replace it
CREATE SCHEMA BACKUP
CREATE TABLE BACKUP.TABLETOSYNC
AS (SELECT * FROM MYLIB.TABLETOSYNC)
--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)
MERGE INTO MYLIB.TABLETOSYNC AS TARGET_TABLE
USING QTEMP.TABLETOSYNC AS SOURCE_TABLE
ON (TARGET_TABLE.TABLEKEYFIELD=SOURCE_TABLE.TABLEKEYFIELD )
WHEN MATCHED THEN
TARGET_TABLE.COL1 = SOURCE_TABLE.COL1
WHEN NOT MATCHED THEN
-- More info:
-- 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.)
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.
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.
$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);
echo odbc_result($result, "MYFIELD");
Ever wanted to use the group profiles that exist on your IBM i to control your PHP applications? Maybe only people in a certain group should see a page. The SQL script below gets the group profiles the current user belongs to (Note: USER is a sql reserved word that gets the current user profile calling sql).
--Group Profile info of current user
SELECT GROUP_PROFILE_NAME, USER_TEXT
WHERE USER_PROFILE_NAME = USER
--Query to view all your users group profiles ordered by user and group
SELECT USER_PROFILE_NAME AS USERNAME,GROUP_PROFILE_NAME AS GROUP,USER_TEXT AS DESCRIPTION
ORDER BY USER_PROFILE_NAME,GROUP_PROFILE_NAME
The PHP code below connects as the current user and checks to see if they are in the ADMIN group. We use $hasAccess to determine if the user has access.
QSYS2.Authids – an alias to AUTHORIZATIONs which calls OBJECT_STATISTICS twice for user profile data.
QSYS2.GROUP_USERS – a function that calls QSYS/QSQGRPPRF
and i think this will fill up your QAUDJRN with Authorization Failures in a secure environment, so QSYS2.USER_INFO might be a better choice and parse out the group profiles under the SUPPLEMENTAL_GROUP_LIST field and GROUP_PROFILE_NAME