Which User Should I Use with Direct Load Materialization?

[former_member201034]

Direct load materialization is an automatic materialization method that loads data directly from a primary table into a replicate table without interrupting the normal replication process.

Using direct load materialization, you can materialize data to an SAP HANA database from SAP ASE, Microsoft SQL Server, Oracle, or DB2 UDB, as well as between two SAP ASE databases. This feature is available beginning in version 15.7.1 SP100 on a replicate SAP Replication Server—or Replication Agent, if you want to materialize data from a primary database that is not an SAP ASE database.

The most common mistakes made when setting up direct load materialization happen because user and password options are not set correctly. This blog will help you identify and avoid these pitfalls.

Let’s review the create subscription syntax for direct load materialization:

create subscription <subscription_name>
for <replication_definition>
with replicate at <dataserver.database>
[where <search_conditions>]
without holdlock
direct_load
[user <cusername> password <cpass>]
go

The <cusername> here doesn’t have to be an SAP Replication Server user, but rather, the user who logs in to the primary SAP ASE database or Replication Agent. For an SAP ASE primary database, this user needs to have select permission on the database table being materialized. For a non-SAP ASE primary database, the user must be the Replication Agent admin user. If you don’t specify the user/password options, the user you logged in to SAP Replication Server with is used to log in to the primary SAP ASE database or Replication Agent.

SAP Replication Server gets its cue to proceed with a direct load materialization with an SAP ASE primary database when it sees a marker activated by <cusername>. But, because the RepAgent thread ignores all database activities that are performed by the maintenance user, if <cusername> is the maintenance user of the primary database, SAP Replication Server never sees the marker. That means that you cannot use the maintenance user of an SAP ASE primary database as your <cusername>.

If you’re using non-SAP ASE primary databases, you can't use the maintenance user as the pds_username, which Replication Agent uses to log in to the primary data server. Refer to SAP Knowledge Base Article (KBA) 2305964 for more information.

If the direct load materialization completes but the subscription is not valid, check to see whether <cusername> matches the maintenance user of the SAP ASE primary database, or whether the pds_username of Replication Agent matches the maintenance user. If it matches, re-create the subscription with another database user, or drop and re-create the Replication Agent connection in SAP Replication Server using a different maintenance user. If it doesn't match, the most likely reason for the invalid subscription is that the Replication Agent truncation point is lagging considerably far behind. Refer to KBA 1988032 for a solution.

To clean up a failed subscription:

1. Verify that the regular replicate DSI is up.


2. Drop the subscription from the replicate SAP Replication Server. For example:
   drop subscription <subname> for <repdefname> with replicate at <rdsname.rdbname> without purge


3. Clean up the replicate table. You can either drop the table or remove all contents from the table.

If the catch-up queue does not go away after you drop the subscription:

1. Drop the connection, for example:
   drop connection to HANADS.rs_01000065800000c7tab_99_sub


2. Drop the catch-up queue:
   sysadmin drop_queue, <queue number>, 0

To help you diagnose problems that occur during direct load materialization, you can turn on the following relevant traces in SAP Replication Server:

• rsfeature, rsfeature_direct_load – traces the flow and progress of subscriptions created with direct_load.


• rsfeature, rsfeature_dl_apply_commands – traces the commands applied to the replicate by the materialization apply threads.


• rsfeature, rsfeature_dl_mat_commands – traces the commands related to direct load materialization.


• sub, sub_markers – traces the subscription markers.


• sub, general_1 – traces general information about subscription.

For more information about direct load materialization, see the SAP Replication Server Administration Guide Volume 1.