Skip to Content
Author's profile photo Kathy Wang

Which User Should I Use with Direct Load Materialization?

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.

Assigned Tags

      3 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Avinash Kothare
      Avinash Kothare

      Kathy

      Excellent inputs on "direct materialization".

      Can the table level subscription be dropped without purge at replicate --- after its status becomes valid  ?

      I have a database level repdef + repsub already in place

      I  would like to add new tables in the mix using direct load method.

      As of now I have to stop activity on primary, bulk materialize at replicate, match row counts and then allow database repdef / repsub to take over.

      Direct load looks  great if users need NOT stop their activity and I do not have to do the bulk load.

      TIA

      Avinash

       

      Avinash

       

      Author's profile photo Kathy Wang
      Kathy Wang
      Blog Post Author

      Hi Avinash,

      Thanks a lot for your interest in direct load materialization. If what you want to do is create a table replication definition and subscription with direct load on top of your existing database repdef and subscription, and then drop the table level subscription after the materialization is done, I'm afraid it's not doable. But you can talk to our tech support to see if they have other solutions for your use case.

      Thanks again for reading the blog.

      Regards,

      Kathy

      Author's profile photo Former Member
      Former Member

      what you want to do is create a table replication definition and subscription with direct load on top of your existing database repdef and subscription, and then drop the table level subscription after the materialization is done, I’m afraid it’s not doable. But you can talk to our tech support to see if they have other solutions for your use case.emoji meaning periodic table of elements electronegativity chart kinemaster pro apk download