| Product: | Syniti Data Replication (Syniti DR, fka DBMoto) |
| Version: | All |
| ID: | 1598 |
| Summary: | The specific profiles and permissions you will need if performing replications using IBM i, System i5, iSeries or AS/400 |
This article describes the specific profiles and permissions you will need if performing replications using IBM i, System i5, iSeries or AS/400.
In some cases, Syniti DR/DBMoto needs to access or run commands requiring elevated permissions. However, the most important thing to remember is that Syniti DR/DBMoto runs on Windows, not on IBM i systems. It is a client application which uses the Host Database Server to access data. This means that you will see the jobs used by Syniti DR/DBMoto as QZDASOINIT jobs and the users as QUSER. There is no job or program that can be run as a standalone i system application. Therefore, the final choice regarding everything Syniti DR/DBMoto does on i system is up to you and how you are using and configuring it.
Typical User Profile:
1. User class . . . . . . *SECOFR or *SYSOPR (for QSECURITY=30 or more)
2. Assistance level . . . *SYSVAL
3. Current library . . . *CRTDFT
4. Initial program to call *NONE
5. Library . . . . . . <empty>
6. Initial menu . . . . . MAIN <does not matter>
7. Library . . . . . . *LIBL <does not matter>
8. Limit capabilities . . *NO
9. Special authority . . . . . . . *ALLOBJ
10. Special environment . . . . . . *SYSVAL
11. Maximum allowed storage . . . . *NOMAX <required, from QUSER>
12. Highest schedule priority . . . 3
13. Job description . . . . . . . . QDFTJOBD <default>
14. Library . . . . . . . . . . . QGPL
15. Group profile . . . . . . . . . *NONE
16. Owner . . . . . . . . . . . . . *USRPRF
17. Group authority . . . . . . . . *NONE
18. Group authority type . . *PRIVATE
The group options are generic, on purpose, along with the *ALLOBJ special authority. You can of course decide to add the user profile to a group and demand specific authorities from the group for specific objects. In this case, you can remove the *SYSOPR and *ALLOBJ authorities and add specific authorities for specific objects. However, this could make Syniti DR/DBMoto configuration a bit difficult on your side, since in designing the replications and in testing mirroring features you could clash with specific permissions on system objects which are used to get catalogs or journal contents).
Note that Syniti DR/DBMoto, during mirroring/synchronization configuration and replication, requires access to journals and receivers where, ideally, receivers are managed by the system (automatically creating new ones when the active ones are full, unbinding the old ones and binding the new ones.)
If you have modifed the typical user profile above, you may find a situation where Syniti DR/DBMoto attempts to access the next receiver but your profile has no permission to act on the request since the permissions aren't varied dynamically as when receivers are managed by the system.
We recommend allowing the Syniti DR/DBMoto user profile access to explore the catalog to avoid the need to continuosly vary the catalog objects used.
Commands Executed by Data Replicator and Management Center
Below you can find the commands executed by the Syniti DR Replication Agent/DBMoto Data Replicator (DR) and Management Center (MC). The commands are all run via QSYS.QCMDEXEC by QZDASOINIT jobs (user QUSER). The values between "<>" are execution-dependent parameters
To get journal information (DR + MC):
- DSPFD FILE(<FileName>) TYPE(*ATR) OUTPUT(*OUTFILE) FILEATR(*PF) OUTFILE(<TempLib>/DBRSTFIS)
- DSPFD FILE(<Library>/*ALL) TYPE(*ATR) OUTPUT(*OUTFILE) FILEATR(*PF) OUTFILE(<TempLib>/DBRSTFIS)
- Select PHJRNL, PHJRLB, PHJRNM, PHJRIM From <TempLib>.DBRSTFIS where PHJRNL = 'Y'
- DSPFFD FILE(<FileName>) OUTPUT(*OUTFILE) OUTFILE(<TempLib>/DBRSTFF)
- SELECT WHALIS, WHFLDI, WHFMT FROM <TempLib>.DBRSTFF
To read the last journal ID and achieve mirroring (DR):
- CALL <DBMotoLibray>.JRNSQNM (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
- select count(*) from qsys2.systables where TABLE_SCHEMA = <library> and TABLE_NAME = <filename>
- DLTF FILE(<TempLib>/<JournalOutFile>)
- DSPJRN JRN(<JournalName>) FILE(<JounalOutFile>) RCVRNG(<CurrReceiverName> <LastReceiver>) NBRENT(..) JRNCDE((R) (F) (D) (J *IGNFILSLT)) FROMENT(...) OUTPUT(*OUTFILE) OUTFILFMT(*TYPE3) OUTFILE(<JournalOutFile>) ENTDTALEN(...) NULLINDLEN(*CALC) TOENT(...)
- select JOSEQN, JOCODE, JOENTT, JOTSTP, JOUSPF, JOOBJ, JOLIB, JOMBR, JOCTRR, JONVI, JOESD from <TempLib>.<JournalOutFile>
- DLTF FILE(<TempLib>/<JournalOutFile>)
To install the required DBMOTOLIB (MC):
This is usually performed by the same user ID that was defined in the System i5/iSeries/AS400 source connection. Installing DBMOTOLIB involves:
- upload via ftp of the SAVF file
- restore DBMOTOLIB (via HSVR; needs QCMDEXEC)
- create stored procedure (via HSVR; needs QCMDEXEC)
Journals, source and target:
- access to source tables in SELECT mode (for refresh/lookups and mirroring/synchro - DR and MC)
- access to source tables journals and receivers with *ALL privileges (for mirroring/synchro - DR and MC)
- access to target tables in SELECT/INSERT/UPDATE/DELETE mode (for refresh/lookups and mirroring/synchro - DR and MC)
- access the AS400 catalog tables and views in QSYS-QSYS2 (for refresh/lookups and mirroring/synchro - DR and MC)
- run stored procedures (for refresh/lookups and mirroring/synchro - DR and MC)
Catalog commands (MC):
These are listed in the Syniti DR/DBMoto file Config/AS400.xml and summarized below. The Config/AS400.xml may be more current than the list below.
The commands are used for retrieving catalog info (schemas/libaries, tables, views, indexes and primary keys.)
<!-- catalogs unsupported -->
DSPOBJD OBJ(QSYS/@schema) OBJTYPE(*LIB) OUTPUT(*OUTFILE) OUTFILE(QTEMP/DBRSTL)
select ODOBNM from QTEMP.DBRSTL order by 1
select ODOBNM from QTEMP.DBRSTL where ODOBNM = '@schema'
<!--schemas name="library" >
select distinct TABLE_SCHEMA from qsys2.systables order by 1
select distinct TABLE_SCHEMA from qsys2.systables where TABLE_SCHEMA = '@schema' order by 1
<!--tables name="table">
DSPFD FILE(@schema/@table) TYPE(*ATR) OUTPUT(*OUTFILE) FILEATR(*PF) OUTFILE(QTEMP/DBRSTPF)
DSPFD FILE(@schema/@table) TYPE(*ATR) OUTPUT(*OUTFILE) FILEATR(*LF) OUTFILE(QTEMP/DBRSTLF)
select PHLIB, PHFILE, 'Table', PHTXT from QTEMP.DBRSTPF
union
select LGLIB, LGFILE, 'View', LGTXT from QTEMP.DBRSTLF
order by 1, 2
<minVersion="0004.0003.0000">
select TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE, TABLE_TEXT, LONG_COMMENT, SYSTEM_TABLE_NAME,
BASE_TABLE_SCHEMA, BASE_TABLE_NAME, BASE_TABLE_MEMBER
from qsys2.systables
where TABLE_SCHEMA = '@schema'
and TABLE_NAME like '@table'
order by 1, 2
<maxVersion="0004.0002.9999">
select TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE, TABLE_TEXT, LONG_COMMENT, SYSTEM_TABLE_NAME
from qsys2.systables
where TABLE_SCHEMA = '@schema'
and TABLE_NAME like '@table'
order by 1, 2
<columns name="column">
select COLUMN_NAME, TABLE_NAME, SYSTEM_TABLE_SCHEMA, ORDINAL_POSITION, DATA_TYPE,
LENGTH, NUMERIC_PRECISION, NUMERIC_SCALE, IS_NULLABLE, 0, HAS_DEFAULT,
COLUMN_DEFAULT, COLUMN_HEADING, COLUMN_TEXT, LONG_COMMENT, CCSID
from qsys2.syscolumns
where SYSTEM_TABLE_SCHEMA = '@schema'
and TABLE_NAME like '@table'
order by 3, 2, 4
select COLUMN_NAME, TABLE_NAME, SYSTEM_TABLE_SCHEMA, ORDINAL_POSITION, DATA_TYPE,
LENGTH, NUMERIC_PRECISION, NUMERIC_SCALE, IS_NULLABLE, 0, HAS_DEFAULT,
COLUMN_DEFAULT, COLUMN_HEADING, COLUMN_TEXT, LONG_COMMENT, CCSID
from qsys2.syscolumns
where SYSTEM_TABLE_SCHEMA = '@schema'
and TABLE_NAME like '@table'
and COLUMN_NAME like '@column'
order by 3, 2, 4
<!-- columnsExtra>
DSPFFD FILE(@schema/@table) OUTPUT(*OUTFILE) OUTFILE(QTEMP/DBRSTFF)
select WHFLDI, WHFMT from QTEMP/DBRSTFF</sql>
<primarykeys name="primarykey">
select D.COLUMN_NAME, M.TABLE_NAME, M.SYSTEM_TABLE_SCHEMA, M.CONSTRAINT_NAME, D.ORDINAL_POSITION
from QSYS2.SYSCST as M, QSYS2.SYSKEYCST as D
where M.CONSTRAINT_NAME = D.CONSTRAINT_NAME and M.CONSTRAINT_SCHEMA = D.CONSTRAINT_SCHEMA
and M.SYSTEM_TABLE_SCHEMA = '@schema'
and M.TABLE_NAME like '@table'
and M.CONSTRAINT_TYPE = 'PRIMARY KEY'
order by 3, 2, 5
select D.COLUMN_NAME, M.TABLE_NAME, M.SYSTEM_TABLE_SCHEMA, M.CONSTRAINT_NAME, D.ORDINAL_POSITION
from QSYS2.SYSCST as M, QSYS2.SYSKEYCST as D
where M.CONSTRAINT_NAME = D.CONSTRAINT_NAME and M.CONSTRAINT_SCHEMA = D.CONSTRAINT_SCHEMA
and M.SYSTEM_TABLE_SCHEMA = '@schema'
and M.TABLE_NAME like '@table'
and D.COLUMN_NAME like '@column'
and M.CONSTRAINT_TYPE= 'PRIMARY KEY'
order by 3, 2, 5
<primarykeysext>
DSPFD FILE(@schema/@table) TYPE(*ACCPTH) OUTPUT(*OUTFILE) FILEATR(*PF *LF) OUTFILE(QTEMP/DBRSTFD)
select S.COLUMN_NAME, S.TABLE_NAME, S.TABLE_SCHEMA, D.APKEYN
from QTEMP.DBRSTFD D
left join qsys2.syscolumns S on S.SYSTEM_TABLE_SCHEMA = D.APLIB
and S.SYSTEM_TABLE_NAME like D.APFILE
and S.SYSTEM_COLUMN_NAME like D.APKEYF
order by 3, 2, 4
select S.COLUMN_NAME, S.TABLE_NAME, S.TABLE_SCHEMA, D.APKEYN
from QTEMP.DBRSTFD D
left join qsys2.syscolumns S on S.SYSTEM_TABLE_SCHEMA = D.APLIB
and S.SYSTEM_TABLE_NAME like D.APFILE
and S.SYSTEM_COLUMN_NAME like D.APKEYF
where S.COLUMN_NAME = '@column'
order by 3, 2, 4