/
API Tables

API Tables

Staging tables

create_glue_table("P_DB" VARCHAR(16777216),
"P_SOURCE" VARCHAR(16777216),
"P_TABLE" VARCHAR(16777216),
"P_TABLE_DEF" VARCHAR(16777216),
"P_TABLE_DESC" VARCHAR(16777216),
"P_CLUSTER" BOOLEAN,
"P_TRANSIENT" BOOLEAN)

create_glue_table("P_DB" VARCHAR(16777216),
"P_SOURCE" VARCHAR(16777216),
"P_TABLE" VARCHAR(16777216),
"P_TABLE_DEF" VARCHAR(16777216),
"P_TABLE_DESC" VARCHAR(16777216),
"P_CLUSTER" BOOLEAN,
"P_TRANSIENT" BOOLEAN)

Create staging table and deploy merge objects

Parameters:

  • P_DB → Database of the staging table

  • P_SOURCE → Schema of the staging table

  • P_TABLE → Name of the staging table

  • P_TABLE_DEF → Table definition.

    Table definition formatting: { -- Example of column definition, each column should have said definition {"fieldname": "<fieldname>", "type": <type>, "key": <true/false>, "skey": <true/false> "comment": "<comment>" } } -- Fieldname = Name of the column -- Type = Data type of the column -- Key = If the column is part of the primary key or not -- SKey = Semantic key, only relevant for cluster tables -- Comment = Column comment

  • P_TABLE_DESC → Table description

  • P_CLUSTER → Is it a cluster table

  • P_TRANSIENT → Is it a transient table

alter_glue_table("P_DB" VARCHAR(16777216),
"P_SOURCE" VARCHAR(16777216),
"P_TABLE" VARCHAR(16777216),
"P_TABLE_DEF" VARCHAR(16777216))

alter_glue_table("P_DB" VARCHAR(16777216),
"P_SOURCE" VARCHAR(16777216),
"P_TABLE" VARCHAR(16777216),
"P_TABLE_DEF" VARCHAR(16777216))

Alters staging table and modifies merge objects accordingly. Only addition of columns is supported.

Parameters:

  • P_DB → Database of the staging table

  • P_SOURCE → Schema of the staging table

  • P_TABLE → Name of the staging table

  • P_TABLE_DEF → Table definition.

    Table definition formatting: { -- Example of column definition, each column should have said definition {"fieldname": "<fieldname>", "type": <type>, "key": <true/false>, "skey": <true/false> "comment": "<comment>" } } -- Fieldname = Name of the column -- Type = Data type of the column -- Key = If the column is part of the primary key or not -- SKey = Semantic key, only relevant for cluster tables -- Comment = Column comment

drop_glue_table("P_DB" VARCHAR(16777216),
"P_SOURCE" VARCHAR(16777216),
"P_TABLE" VARCHAR(16777216))

drop_glue_table("P_DB" VARCHAR(16777216),
"P_SOURCE" VARCHAR(16777216),
"P_TABLE" VARCHAR(16777216))

Deletes the staging table and all associated merge objects except the merge table. Merge table is backed up as <table name>_BAK_<current timestamp> e.g. ZZVBAK_BAK_20230606084229

Parameters:

  • P_DB → Database of the staging table

  • P_SOURCE → Schema of the staging table

  • P_TABLE → Name of the staging table

truncate_glue_table("P_DB" VARCHAR(16777216),
"P_SOURCE" VARCHAR(16777216),
"P_TABLE" VARCHAR(16777216))

truncate_glue_table("P_DB" VARCHAR(16777216),
"P_SOURCE" VARCHAR(16777216),
"P_TABLE" VARCHAR(16777216))

Truncates staging table. Merge table is backed up as <table name>_BAK_<current timestamp> e.g. ZZVBAK_BAK_20230606084229 and new one is created.

Parameters:

  • P_DB → Database of the staging table

  • P_SOURCE → Schema of the staging table

  • P_TABLE → Name of the staging table

delete_selection("P_DB" VARCHAR(16777216),
"P_SOURCE" VARCHAR(16777216),
"P_TABLE" VARCHAR(16777216),
"P_WHERE_COND” VARCHAR(16777216))

delete_selection("P_DB" VARCHAR(16777216),
"P_SOURCE" VARCHAR(16777216),
"P_TABLE" VARCHAR(16777216),
"P_WHERE_COND” VARCHAR(16777216))

Delete part of the data from both staging and merge table. What data is deleted is based on the P_WHERE_COND parameter.

Parameters:

  • P_DB → Database of the staging table

  • P_SOURCE → Schema of the staging table

  • P_TABLE → Name of the staging table

  • P_WHERE_COND → Where condition based on which data is deleted

    Where condition example: "MANDT" = 100 AND "GL_REQUEST" = 1234

delete_merge_inconsistency("P_DB" VARCHAR(16777216),
"P_SOURCE" VARCHAR(16777216),
"P_TABLE" VARCHAR(16777216))

delete_merge_inconsistency("P_DB" VARCHAR(16777216),
"P_SOURCE" VARCHAR(16777216),
"P_TABLE" VARCHAR(16777216))

Deletes data from the merge table that is not present in the staging table. It checks the data based on GL_REQUEST column value. Meaning all rows in the merge table with GL_REQUEST value not present in the staging table are deleted.

Parameters:

  • P_DB → Database of the staging table

  • P_SOURCE → Schema of the staging table

  • P_TABLE → Name of the staging table

switch_trans_to_standard_table("P_DB" VARCHAR(16777216) 
                              "P_SOURCE" VARCHAR(16777216),
                              "P_TABLE" VARCHAR(16777216))
switch_trans_to_standard_table("P_DB" VARCHAR(16777216),
                              "P_SOURCE" VARCHAR(16777216))
switch_trans_to_standard_table("P_DB" VARCHAR(16777216))

switch_trans_to_standard_table("P_DB" VARCHAR(16777216) 
                              "P_SOURCE" VARCHAR(16777216),
                              "P_TABLE" VARCHAR(16777216))
switch_trans_to_standard_table("P_DB" VARCHAR(16777216),
                              "P_SOURCE" VARCHAR(16777216))
switch_trans_to_standard_table("P_DB" VARCHAR(16777216))

Transient tables will be recreated as standard tables (the data will remain untouched).

Parameters:

  • P_DB → Database of the staging table

  • P_SOURCE → Schema of the staging table

  • P_TABLE → Name of the staging table

Metadata

add_active_table( "P_DB" VARCHAR(16777216),
"P_SOURCE" VARCHAR(16777216),
"P_TABLE" VARCHAR(16777216),
"P_PK" VARCHAR(16777216),
"P_SK" VARCHAR(16777216),
"P_CLUSTER" BOOLEAN,
"P_TRANSIENT" BOOLEAN default false)

add_active_table( "P_DB" VARCHAR(16777216),
"P_SOURCE" VARCHAR(16777216),
"P_TABLE" VARCHAR(16777216),
"P_PK" VARCHAR(16777216),
"P_SK" VARCHAR(16777216),
"P_CLUSTER" BOOLEAN,
"P_TRANSIENT" BOOLEAN default false)

Adds metadata entry to the active_tables table.

Parameters:

  • P_DB → Database of the staging table

  • P_SOURCE → Schema of the staging table

  • P_TABLE → Name of the staging table

  • P_PK → Primary key

  • P_SK → Semantic key

  • P_CLUSTER → Is it cluster table flag

  • P_TRANSIENT → Is it transient table flag

get_active_tables()
get_active_tables("P_ACTIVE" BOOLEAN )
get_active_tables("P_DB" VARCHAR(16777216)
"P_SOURCE" VARCHAR(16777216))
get_active_table("P_DB" VARCHAR(16777216),
"P_SOURCE" VARCHAR(16777216),
"P_TABLE" VARCHAR(16777216))

get_active_tables()
get_active_tables("P_ACTIVE" BOOLEAN )
get_active_tables("P_DB" VARCHAR(16777216)
"P_SOURCE" VARCHAR(16777216))
get_active_table("P_DB" VARCHAR(16777216),
"P_SOURCE" VARCHAR(16777216),
"P_TABLE" VARCHAR(16777216))

Get data from the active_tables table.

Parameters:

  • P_DB → Database of the staging table

  • P_SOURCE → Schema of the staging table

  • P_TABLE → Name of the staging table

  • P_ACTIVE → Flag if merge task is suspended or not

remove_active_tables("P_DB" VARCHAR(16777216)
"P_SOURCE" VARCHAR(16777216))
remove_active_table("P_DB" VARCHAR(16777216),
"P_SOURCE" VARCHAR(16777216),
"P_TABLE" VARCHAR(16777216))

remove_active_tables("P_DB" VARCHAR(16777216)
"P_SOURCE" VARCHAR(16777216))
remove_active_table("P_DB" VARCHAR(16777216),
"P_SOURCE" VARCHAR(16777216),
"P_TABLE" VARCHAR(16777216))

Remove entry or entries from the active_tables table.

Parameters:

  • P_DB → Database of the staging table

  • P_SOURCE → Schema of the staging table

  • P_TABLE → Name of the staging table

set_table_active("P_DB" VARCHAR(16777216)
"P_SOURCE" VARCHAR(16777216),
"P_TABLE" ARRAY)
set_table_active("P_DB" VARCHAR(16777216),
"P_SOURCE" VARCHAR(16777216),
"P_TABLE" VARCHAR(16777216))

set_table_active("P_DB" VARCHAR(16777216)
"P_SOURCE" VARCHAR(16777216),
"P_TABLE" ARRAY)
set_table_active("P_DB" VARCHAR(16777216),
"P_SOURCE" VARCHAR(16777216),
"P_TABLE" VARCHAR(16777216))

Set table(s) as active, meaning merge task is running, in the active_table table.

Parameters:

  • P_DB → Database of the staging table

  • P_SOURCE → Schema of the staging table

  • P_TABLE (VARCHAR) → Name of the staging table

  • P_TABLE (ARRAY) → Array of staging table names

set_table_inactive("P_DB" VARCHAR(16777216)
"P_SOURCE" VARCHAR(16777216),
"P_TABLE" ARRAY)
set_table_inactive("P_DB" VARCHAR(16777216),
"P_SOURCE" VARCHAR(16777216),
"P_TABLE" VARCHAR(16777216))

set_table_inactive("P_DB" VARCHAR(16777216)
"P_SOURCE" VARCHAR(16777216),
"P_TABLE" ARRAY)
set_table_inactive("P_DB" VARCHAR(16777216),
"P_SOURCE" VARCHAR(16777216),
"P_TABLE" VARCHAR(16777216))

Set table(s) as inactive, meaning merge task is suspended, in the active_table table.

Parameters:

  • P_DB → Database of the staging table

  • P_SOURCE → Schema of the staging table

  • P_TABLE (VARCHAR) → Name of the staging table

  • P_TABLE (ARRAY) → Array of staging table names