How can I unload NSM calendar, OPR Message Records and Actions (MRAs) and Workload assets tables from the 11.2 postgres MDB in ASCII format that I can then load back into another NSM 11.2 postgres MDB?

Products

• CA Unicenter NSM

Issue/Introduction

Description:

In NSM 3.1 I could unload/load tables in ASCII format from the CADB databases using commands similar to the following:

To unload Event MRA tables:

sql-> connect to caiunidb;
sql-> begin work;
sql-> unload external file1 table cadb.opra_msg;
Y
sql-> unload external file2 table cadb.opra_act;
Y
sql-> unload external file3 table cadb.opra_ctl;
Y
sql-> commit work;
sql-> release;
sql-> quit;
 
To load the dumped Event MRA tables:
 
sql-> connect to caiunidb;
sql-> delete from cadb.opra_msg;
sql-> delete from cadb.opra_act;
sql-> delete from cadb.opra_ctl;
sql-> load external file1 table cadb.opra_msg;
Y
sql-> load external file2 table cadb.opra_actl
Y
sql-> load external file3 table cadb.opra_ctl;
Y
sql-> release;
sql-> quit;

What psql commands will allow me to execute the same for NSM 11.2 calendars, OPR MRAs, and Workload assets stored in the 11.2 postgres MDB?

Solution:

To unload NSM 11.2 postgres MDB tables, first shutdown all of NSM and then start the postgres database on the system where you wish to unload the tables:

uinishutdown all
unistart postgres
unifstat
 
              CA Services Status Report
 
          Component Name               Pid        Status
------------------------------------  -------  --------------
Postgres                               327708  running
 
 
Once you have confirmed that the postgres database is running, you can unload the NSM tables as follows:
 
Calendars:
pg_dump -U postgres -a -t mdbadmin.cal -f cal.sql mdb 
 
Event MRAs:
pg_dump -U postgres -a -t mdbadmin.opra_act -f opra_act.sql mdb
pg_dump -U postgres -a -t mdbadmin.opra_ctl -f opra_ctl.sql mdb
pg_dump -U postgres -a -t mdbadmin.opra_msg -f opra_msg.sql mdb
 
Workload Assets:
pg_dump -U postgres -a -t mdbadmin.jmo_station -f jmo_station.sql mdb
pg_dump -U postgres -a -t mdbadmin.jmo_group -f jmo_group.sql mdb
pg_dump -U postgres -a -t mdbadmin.jmo_groupid -f jmo_groupid.sql mdb
pg_dump -U postgres -a -t mdbadmin.jmo_gbl -f jmo_gbl.sql mdb
pg_dump -U postgres -a -t mdbadmin.jmo_gen -f jmo_gen.sql mdb
pg_dump -U postgres -a -t mdbadmin.jmo_jbr -f jmo_jbr.sql mdb
pg_dump -U postgres -a -t mdbadmin.jmo_jhr -f jmo_jhr.sql mdb
pg_dump -U postgres -a -t mdbadmin.jmo_jtr -f jmo_jtr.sql mdb
pg_dump -U postgres -a -t mdbadmin.jmo_jwb -f jmo_jwb.sql mdb
pg_dump -U postgres -a -t mdbadmin.jmo_mwc -f jmo_mwc.sql mdb
pg_dump -U postgres -a -t mdbadmin.jmo_mwg -f jmo_mwg.sql mdb
pg_dump -U postgres -a -t mdbadmin.jmo_nod -f jmo_nod.sql mdb
pg_dump -U postgres -a -t mdbadmin.jmo_prb -f jmo_prb.sql mdb
pg_dump -U postgres -a -t mdbadmin.jmo_prt -f jmo_prt.sql mdb
pg_dump -U postgres -a -t mdbadmin.jmo_psrm -f jmo_psrm.sql mdb
pg_dump -U postgres -a -t mdbadmin.jmo_psrt -f jmo_psrt.sql mdb
pg_dump -U postgres -a -t mdbadmin.jmo_sbr -f jmo_sbr.sql mdb
pg_dump -U postgres -a -t mdbadmin.jmo_shr -f jmo_shr.sql mdb
pg_dump -U postgres -a -t mdbadmin.jmo_srq -f jmo_srq.sql mdb
pg_dump -U postgres -a -t mdbadmin.jmo_str -f jmo_str.sql mdb
pg_dump -U postgres -a -t mdbadmin.jmo_swb -f jmo_swb.sql mdb
pg_dump -U postgres -a -t mdbadmin.jmo_trg -f jmo_trg.sql mdb
pg_dump -U postgres -a -t mdbadmin.jmo_trt -f jmo_trt.sql mdb
pg_dump -U postgres -a -t mdbadmin.jmo_systemdeps -f jmo_systemdeps.sql mdb
pg_dump -U postgres -a -t mdbadmin.jmo_tsystemdeps -f jmo_tsystemdeps.sql mdb

Please note that the cal.sql table that you dump will contain binary data because the binary representation of the calendar and the calendar commands are stored in the MDB in binary format. You will not be able to modify these columns of your defined calendars. You should be able to modify the id and fixed_year columns if necessary. All other tables that have been unloaded are in ASCII format.

Before loading the dumped NSM 11.2 tables into the 11.2 postgres MDB, first shutdown all of NSM and then start the postgres database on the system where you wish to load the dumped tables:

uinishutdown all
unistart postgres
 
You can then reload this data into the NSM 11.2 MDB using the following set of commands:
 
Calendars:
psql -c "delete from cal;" mdb mdbadmin
psql -f cal.sql mdb mdbadmin
 
Event MRAs:
psql -c "delete from opra_act;" mdb mdbadmin
psql -f opra_act.sql mdb mdbadmin
psql -c "delete from opra_ctl;" mdb mdbadmin
psql -f opra_ctl.sql mdb mdbadmin
psql -c "delete from opra_msg;" mdb mdbadmin
psql -f opra_msg.sql mdb mdbadmin
 
Workload Assets:
psql -c "delete from jmo_station;" mdb mdbadmin
psql -f jmo_station.sql mdb mdbadmin
psql -c "delete from jmo_group;" mdb mdbadmin
psql -f jmo_group.sql mdb mdbadmin
psql -c "delete from jmo_groupid;" mdb mdbadmin
psql -f jmo_groupid.sql mdb mdbadmin
psql -c "delete from jmo_gbl;" mdb mdbadmin
psql -f jmo_gbl.sql mdb mdbadmin
psql -c "delete from jmo_gen;" mdb mdbadmin
psql -f jmo_gen.sql mdb mdbadmin
psql -c "delete from jmo_jbr;" mdb mdbadmin
psql -f jmo_jbr.sql mdb mdbadmin
psql -c "delete from jmo_jhr;" mdb mdbadmin
psql -f jmo_jhr.sql mdb mdbadmin
psql -c "delete from jmo_jtr;" mdb mdbadmin
psql -f jmo_jtr.sql mdb mdbadmin
psql -c "delete from jmo_jwb;" mdb mdbadmin
psql -f jmo_jwb.sql mdb mdbadmin
psql -c "delete from jmo_mwc;" mdb mdbadmin
psql -f jmo_mwc.sql mdb mdbadmin
psql -c "delete from jmo_mwg;" mdb mdbadmin
psql -f jmo_mwg.sql mdb mdbadmin
psql -c "delete from jmo_nod;" mdb mdbadmin
psql -f jmo_nod.sql mdb mdbadmin
psql -c "delete from jmo_prb;" mdb mdbadmin
psql -f jmo_prb.sql mdb mdbadmin
psql -c "delete from jmo_prt;" mdb mdbadmin
psql -f jmo_prt.sql mdb mdbadmin
psql -c "delete from jmo_psrm;" mdb mdbadmin
psql -f jmo_psrm.sql mdb mdbadmin
psql -c "delete from jmo_psrt;" mdb mdbadmin
psql -f jmo_psrt.sql mdb mdbadmin
psql -c "delete from jmo_sbr; in ASCII format" mdb mdbadmin
psql -f jmo_sbr.sql mdb mdbadmin
psql -c "delete from jmo_shr;" mdb mdbadmin
psql -f jmo_shr.sql mdb mdbadmin
psql -c "delete from jmo_srq;" mdb mdbadmin
psql -f jmo_srq.sql mdb mdbadmin
psql -c "delete from jmo_str;" mdb mdbadmin
psql -f jmo_str.sql mdb mdbadmin
psql -c "delete from jmo_swb;" mdb mdbadmin
psql -f jmo_swb.sql mdb mdbadmin
psql -c "delete from jmo_trg;" mdb mdbadmin
psql -f jmo_trg.sql mdb mdbadmin
psql -c "delete from jmo_trt;" mdb mdbadmin
psql -f jmo_trt.sql mdb mdbadmin
psql -c "delete from jmo_systemdeps;" mdb mdbadmin
psql -f jmo_systemdeps.sql mdb mdbadmin
psql -c "delete from jmo_tsystemdeps;" mdb mdbadmin
psql -f jmo_tsystemdeps.sql mdb mdbadmin

Environment

Release:
Component: CCSEM