create connection with using profile clause uses predefined information to configure the connection between Replication Server and a non-Adaptive Server database, and, if needed, to modify the RSSD and the named data_server.database. To create a connection to Adaptive Server, see create connection.
create connection to data_server.database using profile connection_profile[;version] set username [to] user [other_create_connection_options] [display_only]
The data server that holds the database to be added to the replication system.
The database to be added to the replication system.
Indicates the connection profile that you want to use to configure a connection, modify the RSSD, and build replicate database objects.
Specifies the connection profile version to use.
The login name of the Replication Server maintenance user for the database. Replication Server uses this login name to maintain replicated data. You must specify a user name if network-based security is not enabled.
Use the other create connection options to set connection options not specified in the profile, such as setting your password, or to override options specified in the profile, such as specifying a custom function string class to override the function string class provided in Replication Server. For a complete list of the other create connection options, see create connection.
Use display_only with the using profile clause to display the commands that will be executed and the names of the servers upon which the commands will be executed. See the client and Replication Server logs for the result of using display_only.
Creates a connection to an Oracle replicate database:
create connection to oracle.instance using profile rs_ase_to_oracle;standard set username to ora_maint set password to ora_maint_pwd
Creates a connection to a Microsoft SQL Server replicate database that is also a primary database. In this example, the command replaces any error class setting provided by the connection profile with the my_msss_error_class error class:
create connection to msss_server.msss_db using profile rs_ase_to_msss set username to msss_maint set password to msss_maint_pwd set error class to my_msss_error_class with log transfer on
Creates a connection to a DB2 replicate database using a specific version of the profile—v9_1. In this example, the command overrides the command batch size provided by the connection profile with a new value—16384:
create connection to db2.subsys using profile rs_ase_to_db2;v9_1 set username to db2_maint set password to db2_maint_pwd set dsi_cmd_batch_size to ‘16384’
Use the display_only option to show the commands that will be executed if you use a particular profile. The commands and the command output display on your screen and are also written to the Replication Server log:
create connection to oracle.instance using profile rs_ase_to_oracle;standard set username to ora_maint set password to ora_maint_pwd display_only go
Display only using Connection Profile rs_ase_to_oracle;standard.
Command(s) intended for: prs01
create connection to oracle.instance
set error class to rs_oracle_error_class
set function string class to rs_oracle_function_class
set username to ora_maint
set password to ********
set batch to off
Command(s) intended for 'edsprs01.edbprs01':
delete from rs_translation where classid = 0x0000000001000007 and
source_dtid = 0x000000000000000c
Command(s) intended for 'edsprs01.edbprs01':
insert rs_translation (prsid, classid, type, source_dtid, target_dtid,
target_length, target_status, rowtype)
values (0, 0x0000000001000007, 'D', 0x000000000000000c, 0x0000000000010200,
19, 0, 0)
Command(s) intended for 'edsprs01.edbprs01':
delete from rs_translation where classid = 0x0000000001000007 and
source_dtid = 0x000000000000000d
Command(s) intended for 'edsprs01.edbprs01':
insert rs_translation (prsid, classid, type, source_dtid, target_dtid,
target_length, target_status, rowtype)
values (0, 0x0000000001000007, 'D', 0x000000000000000d, 0x0000000000010200,
19, 0, 0)
Command(s) intended for 'edsprs01.edbprs01':
delete from rs_translation where classid = 0x0000000001000007 and
source_dtid = 0x0000000000000001
Command(s) intended for 'edsprs01.edbprs01':
insert rs_translation (prsid, classid, type, source_dtid, target_dtid,
target_length, target_status, rowtype)
values (0, 0x0000000001000007, 'D', 0x0000000000000001, 0x0000000000010202,
0, 0, 0)
Command(s) intended for 'edsprs01.edbprs01':
delete from rs_translation where classid = 0x0000000001000007 and
source_dtid = 0x0000000000000013
Command(s) intended for 'edsprs01.edbprs01':
insert rs_translation (prsid, classid, type, source_dtid, target_dtid,
target_length, target_status, rowtype)
values (0, 0x0000000001000007, 'D', 0x0000000000000013, 0x0000000000010202,
0, 0, 0)
Command(s) intended for 'edsprs01.edbprs01':
delete from rs_translation where classid = 0x0000000001000007 and
source_dtid = 0x000000000000000E
Command(s) intended for 'edsprs01.edbprs01':
insert rs_translation (prsid, classid, type, source_dtid, target_dtid,
target_length, target_status, rowtype)
values (0, 0x0000000001000007, 'D', 0x000000000000000E, 0x0000000000010205,
136, 0, 0)
Command(s) intended for 'edsprs01.edbprs01':
delete from rs_translation where classid = 0x0000000001000007 and
source_dtid = 0x000000000000000F
Command(s) intended for 'edsprs01.edbprs01':
insert rs_translation (prsid, classid, type, source_dtid, target_dtid,
target_length, target_status, rowtype)
values (0, 0x0000000001000007, 'D', 0x000000000000000f, 0x0000000000010205,
136, 0, 0)
Command(s) intended for 'edsprs01.edbprs01':
delete from rs_translation where classid = 0x0000000001000007 and
source_dtid = 0x000000000000001b
Command(s) intended for 'edsprs01.edbprs01':
insert rs_translation (prsid, classid, type, source_dtid, target_dtid,
target_length, target_status, rowtype)
values (0, 0x0000000001000007, 'D', 0x000000000000001b, 0x0000000000010201,
9, 0, 0)
Command(s) intended for 'edsprs01.edbprs01':
delete from rs_translation where classid = 0x0000000001000007 and
source_dtid = 0x000000000000001c
Command(s) intended for 'edsprs01.edbprs01':
insert rs_translation (prsid, classid, type, source_dtid, target_dtid,
target_length, target_status, rowtype)
values (0, 0x0000000001000007, 'D', 0x000000000000001c, 0x0000000000010200,
19, 0, 0)
Command(s) intended for 'oracle.instance':
drop table rs_info
Command(s) intended for 'oracle.instance':
commit
Command(s) intended for 'oracle.instance':
create table rs_info (rskey varchar2 (20), rsval varchar2 (20))
Command(s) intended for 'oracle.instance':
commit
Command(s) intended for 'oracle.instance':
insert into rs_info values ('charset_name', 'iso_1')
Command(s) intended for 'oracle.instance':
insert into rs_info values ('sortorder_name', 'bin_iso_1')
Command(s) intended for 'oracle.instance':
commit
Command(s) intended for 'oracle.instance':
drop public synonym rs_lastcommit
Command(s) intended for 'oracle.instance':
commit
Command(s) intended for 'oracle.instance':
drop table rs_lastcommit
Command(s) intended for 'oracle.instance':
commit
Command(s) intended for 'oracle.instance':
create table rs_lastcommit(origin number(8),origin_qid char(72),
secondary_qid char(72),origin_time date,
dest_commit_time date)
Command(s) intended for 'oracle.instance':
commit
Command(s) intended for 'oracle.instance':
grant all on rs_lastcommit to public
Command(s) intended for 'oracle.instance':
commit
Command(s) intended for 'oracle.instance':
create public synonym rs_lastcommit for rs_lastcommit
Command(s) intended for 'oracle.instance':
commit
Command(s) intended for 'oracle.instance':
CREATE OR REPLACE PROCEDURE
RS_UPDATE_SEQUENCE(SequenceName VARCHAR2, SequenceValue NUMBER,
Increment NUMBER)
AS CurrentID NUMBER; LastID NUMBER; SeqCursor INTEGER; SQLStmt
VARCHAR2(1024);
Result NUMBER;
BEGIN
SQLStmt := 'SELECT ' || SequenceName || '.NEXTVAL FROM DUAL';
SeqCursor := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(SeqCursor,SQLStmt,DBMS_SQL.NATIVE);
DBMS_SQL.DEFINE_COLUMN(SeqCursor, 1, LastID);
Result := DBMS_SQL.EXECUTE_AND_FETCH(SeqCursor);
DBMS_SQL.COLUMN_VALUE(SeqCursor,1,CurrentID);
LOOP
IF ( Increment < 0 ) THEN EXIT WHEN CurrentID <= SequenceValue;
EXIT WHEN CurrentID > LastID;
ELSE EXIT WHEN CurrentID >= SequenceValue;
EXIT WHEN CurrentID < LastID;
END IF;
LastID := CurrentID;
Result := DBMS_SQL.EXECUTE_AND_FETCH(SeqCursor);
DBMS_SQL.COLUMN_VALUE(SeqCursor,1,CurrentID);
END
LOOP;
DBMS_SQL.CLOSE_CURSOR(SeqCursor);
END;
Command(s) intended for 'oracle.instance':
grant all on RS_UPDATE_SEQUENCE to public
Command(s) intended for 'oracle.instance':
DROP sequence rs_ticket_seq
Command(s) intended for 'oracle.instance':
CREATE sequence rs_ticket_seq
Command(s) intended for 'oracle.instance':
Drop table rs_ticket_history
Command(s) intended for 'oracle.instance':
CREATE TABLE rs_ticket_history(cnt numeric(8,0, h1 varchar(10,
h2 varchar(10), h3 varchar(10), h4 varchar(50), pdb varchar(30),
prs varchar(30), rrs varchar(30), rdb varchar(30), pdb_t date,
exec_t date, dist_t date, rsi_t date, dsi_t date,
rdb_t date default current_date, exec_b int, rsi_b int, dsi_tnx int,
dsi_cmd int, ticket varchar(1024))
Command(s) intended for 'oracle.instance':
create unique index rs_ticket_idx on rs_ticket_history(cnt)
Command(s) intended for 'oracle.instance':
create or replace trigger rs_ticket_tri
before insert on rs_ticket_history
for each row
begin
if :new.cnt is null then
select rs_ticket_seq.nextval into :new.cnt from dual;
end if;
end rs_ticket_tri;Command(s) intended for 'oracle.instance':
grant all on rs_ticket_history to public
Command(s) intended for 'oracle.instance':
commit
Connection profiles specify the function-string class and the error class. Connection profiles can also specify other connection options such as whether commands should be batched and what command separator to use. In addition to connection settings, connection profiles can specify class-level translations to install in the RSSD and objects, such as the rs_lastcommit table, to be created in the replicate database.
When you create a connection using a connection profile, the system table services (STS) caches are refreshed so that you do not need to restart Replication Server.
Always specify the set username clause right after the using profile clause.
