Code Snippet: Getting ODI Details for Source/Target System

There are different scenarios where you might need to interact with your source system. For example, we want to perform a delta extract. For that purpose, we have a timestamp column in our source table/view. In our solution, we need to execute a query against the source system to get only the entities which have data generated after specific timestamp.

Also, you may want to export your data to a target table. You will need to connect and execute an insert statement.

For source systems, the connection details are stored in the ODI tables of the FDMEE database. Basically, what you setup in ODI topology. If using a relational database as a target system, you may want to store the connection details in ODI to avoid hard-coding and making it more dynamic.

This version uses the current context you setup in the source system page. We also wanted to have to logical schema as a parameter

Let's have a look!

SQL query to get the ODI details for specific source system:

def get_odi_source_details(fdmAPI, fdmContext, sourceSystemName, logicalSchema):
    '''
     Snippet:      Get ODI Source details for Source System Name 
     Author:       Francisco Amores
     Date:         21/05/2019
           
     Parameters:   
                   - fdmAPI: FDMEE API object
                   - fdmContext: FDM Context object
                   - sourceSystemName: Source System name 
                   - logicalSchema: ODI Logical Schema
                   
     Notes:         This snippet can be pasted in any event script. The function

                    returns a map object with the different properties

     
     FDMEE Version: 11.1.2.3 and later
               
     ----------------------------------------------------------------------
     Change:
     Author:
     Date:
    '''
    
    # *******************************************
    # Import section
    # *******************************************
    from java.sql import SQLException
    
       
    # *******************************************
    # Get ODI Details for Source System
    # *******************************************
    
    # log
    logMsg = "Getting ODI details for Source System %s" % (sourceSystemName)
    fdmAPI.logInfo(logMsg)
    
    sqlOdiDetails = """SELECT
                        S.SOURCE_SYSTEM_NAME,
                        C.CONTEXT_CODE AS ODI_CONTEXT,
                        CO.CON_NAME AS DATA_SERVER_NAME,
                        L.LSCHEMA_NAME AS LOGICAL_SCHEMA,
                        P.SCHEMA_NAME AS PHYSICAL_SCHEMA,
                        TXT.FULL_TXT AS JAVA_URL,
                        CO.JAVA_DRIVER,
                        CO.USER_NAME,
                        CO.PASS AS ENCRYPTED_PWD
                    FROM 
                        AIF_SOURCE_SYSTEMS S INNER JOIN SNP_CONTEXT C
                            ON S.ODI_CONTEXT_CODE = C.CONTEXT_CODE
                        INNER JOIN SNP_LSCHEMA L
                            ON L.LSCHEMA_NAME = ?
                        INNER JOIN SNP_PSCHEMA_CONT PC
                            ON PC.I_CONTEXT = C.I_CONTEXT AND
                               PC.I_LSCHEMA = L.I_LSCHEMA
                        INNER JOIN SNP_PSCHEMA P
                            ON P.I_PSCHEMA = PC.I_PSCHEMA
                        INNER JOIN SNP_CONNECT CO
                            ON P.I_CONNECT = CO.I_CONNECT
                        INNER JOIN SNP_MTXT TXT
                            ON CO.I_TXT_JAVA_URL = TXT.I_TXT
                        LEFT OUTER JOIN SNP_CONNECT_PROP CP
                            ON CP.I_CONNECT = CO.I_CONNECT
                    WHERE S.SOURCE_SYSTEM_NAME = ?"""
    
    # params
    params = [logicalSchema, sourceSystemName]
    
    try:
        # execute SQL query
        rsOdiDetails = fdmAPI.executeQuery(sqlOdiDetails, params)
        
        # initialize map
        mapOdiDetails = {}
        
        # loop
        if rsOdiDetails.isBeforeFirst():
            while rsOdiDetails.next():
                # get ODI details
                mapOdiDetails["ODI_CONTEXT"] = rsOdiDetails.getString("ODI_CONTEXT")
                mapOdiDetails["DATA_SERVER_NAME"]= rsOdiDetails.getString("DATA_SERVER_NAME")
                mapOdiDetails["PHYSICAL_SCHEMA"]= rsOdiDetails.getString("PHYSICAL_SCHEMA")
                mapOdiDetails["JAVA_URL"] = rsOdiDetails.getString("JAVA_URL")
                mapOdiDetails["JAVA_DRIVER"] = rsOdiDetails.getString("JAVA_DRIVER")
                mapOdiDetails["USER_NAME"] = rsOdiDetails.getString("USER_NAME")
                mapOdiDetails["ENCRYPTED_PWD"] = rsOdiDetails.getString("ENCRYPTED_PWD")
               
                # log
                fdmAPI.logInfo("ODI Details: %s" % mapOdiDetails)
        else:
            # ODI Details not found                        
            errMsg = "ODI Details not found for Source System name %s (Logical Schema %s)" % sourceSystemName, logicalSchema
            fdmAPI.logInfo(errMsg)
            raise RuntimeError(errMsg)        
        # close rs
        fdmAPI.closeResultSet(rsOdiDetails)
    except SQLException, ex:
        errMsg = "Error executing the SQL Statement: %s" % ex
        raise RuntimeError(errMsg)
        
    # return
    return mapOdiDetails

Code snippets for FDMEE can be downloaded from GitHub.

Code Snippet: Executing PL/SQL Stored Procedures with IN/OUT Parameters

Do you need to execute a stored procedure from you script? Maybe to populate the Open Interface Table? You can make use of the FDMEE API method executePLSQL but only if the stored procedure does not return OUT parameters. If you need to return any value, then you can use the Java classes for SQL.

The following snippet shows how to execute the procedure remotely from a dblink. Executing the procedure from the database you connect, follows the same approach.

Let's have a look!

Executing a stored procedure with IN/OUT parameters

'''
 Snippet:       Execute a PL/SQL stored procedures IN/OUT params
 Author:        Francisco Amores
 Date:          24/11/2017
 Blog:          http://fishingwithfdmee.blogspot.com
 
 Notes:         This snippet can be pasted in any event script.
                Content of fdmContext object will be logged in the
                FDMEE process log (...\outbox\logs\)
                
                This snippet executes the stored procedure via dblink
                Local stored procedures are executed in a similar way
                
 Instructions:  Set log level (global or application settings) to > 4 
 Hints:         You can implement also code to get db connection details
                instead of hard-coding
               
 FDMEE Version: 11.1.2.3 and later
 ----------------------------------------------------------------------
 Change:
 Author:
 Date:
'''
try:
    # Import Java libraries
    import java.sql.SQLException as SQLException
    import java.sql.DriverManager as SQLDriverMgr
    import java.sql.CallableStatement as SQLCallableStmt
    import java.sql.Types as SQLTypes
    import java.sql.Date as SQLDate # needed for DATE parameters
    import java.text.SimpleDateFormat as SimpleDateFormat
    
    # Note: import any other class you need
    
except ImportError, err:
    errMsg = "Error importing libraries: %s" % err
    fdmAPI.logFatal(errMsg)
    raise RuntimeError(errMsg)

# ----------------------------------------
# Connect to FDMEE or External database
# ----------------------------------------

# Connection details
dbConn = "the jdbc url"
dbUser = "the db user"
dbPasswd = "the db password"

try:        
    # get connection to database for callable statements        
    conn = SQLDriverMgr.getConnection(dbConn, dbUser, dbPasswd)
    fdmAPI.logInfo("Connected to the database")
except SQLException, ex:
    errMsg = "Error executing SQL: %s" % (ex)
    raise RuntimeError("Error generated from FDMEE script\n%s" % errMsg)

# ----------------------------------------
# Execute PL/SQL Stored Procedure
# ----------------------------------------

# Get dblink
dbLink = "your dblink"

# PL/SQL Block Code (via DBLINK)
'''
    Procedure implemeted as:
    PROCEDURE CARGA_TABLA(P1            OUT VARCHAR2,
                          P2            OUT NUMBER,
                          P3            IN NUMBER,
                          P4            IN DATETIME,
                          P5            IN VARCHAR2,
                          P6            IN VARCHAR2,
                          P7            IN VARCHAR2,
                          P8            IN VARCHAR2,                          
                          P9            IN VARCHAR2)
'''

# Each ? represents one stored proc parameter
# Ex: schema.package.storedproc if your stored proc is in a package
plSqlBlock = "{CALL schema.package.storedproc@%s(?, ?, ?, ?, ?, ?, ?, ?, ?)}" % dbLink

# Get parameters for the statement
p3 = "valuep3"
# parameter p4 must be passed as java.sql.Date
sdf = SimpleDateFormat("dd/MM/yyyy")
dtParsed = sdf.parse("date value")
p4 = SQLDate(dtParsed.getTime())
p5 = "this param is passed as null"
p6 = "valuep6"
p7 = "valuep7"
p8 = "valuep8"
p9 = "valuep9"

# Prepare and execute call
try:
    # Callable Statement    
    callableStmt = conn.prepareCall(plSqlBlock)
    fdmAPI.logInfo("Callable statement successfully prepared")
    
    # Set IN parameters
    callableStmt.setBigDecimal("p3", p3)    
    callableStmt.setDate("p4", p4)
    callableStmt.setNull("p5", SQLTypes.VARCHAR) # NULL
    callableStmt.setString("p6", p6)
    callableStmt.setString("p7", p7)
    callableStmt.setString("p8", p7)
    callableStmt.setString("p9", p7)      
    fdmAPI.logInfo("Parameters IN set")
    
    # Register OUT parameters
    callableStmt.registerOutParameter("p1", SQLTypes.VARCHAR)    
    callableStmt.registerOutParameter("p2", SQLTypes.NUMERIC)
    fdmAPI.logInfo("Parameters OUT registered")
        
    # Execute PL/SQL Stored Procedure
    result = callableStmt.execute()
    conn.commit()
    fdmAPI.logInfo("Stored Proceedure successfully executed: %s" % result)
    
    # Get OUT parameters
    p1 = callableStmt.getString("p1")
    p2 = callableStmt.getInt("p2")

    # Log OUT parameters
    fdmAPI.logInfo("OUT p1:  %s" % p1)
    fdmAPI.logInfo("OUT p2:  %s" % p2)
    
except (Exception, SQLException), ex:
    errMsg = "Error when executing the stored procedure: %s" % ex
    fdmAPI.logFatal(errMsg)
    if len(errMsg) <= 1000:
        fdmAPI.showCustomMessage(errMsg)
    raise RuntimeError(errMsg)

# ----------------------------------------
# Close connection
# ----------------------------------------
if callableStmt is not None:
    callableStmt.close()
if conn is not None:
    conn.close()
    fdmAPI.logInfo("DB connection closed")

Code snippets for FDMEE can be downloaded from GitHub.

Code Snippet: merging files by writing chunks

Do you have multiple source files that have to be merged so you can import them as a single one? This requirement is quite common, especially in automated data loads.

For example, our ERP system is exporting two files for Balance Sheet and Profit & Loss. We want to import them as a single file under the same POV. Merging the source files is the solution.

The approach taken is to merge files by writing chunks into target file. In this way, we avoid memory issues when having large source files.

Let's have a look!

Merging a list of files by writing chunks

'''
 Snippet:       Merge a list of files
 Author:        Francisco Amores
 Date:          23/05/2016
 Blog:          http://fishingwithfdmee.blogspot.com
 
 Notes:         This snippet can be pasted in any event script.                
                
 Instructions:  Set log level (global or application settings) to > 4 
 Hints:         Use this snippet to merge multiple single files into
                ont.
                It write chunks to avoid memory issues with large files
               
 FDMEE Version: 11.1.2.3 and later
 ----------------------------------------------------------------------
 Change:
 Author:
 Date:
'''

# initialize
srcFolder = r"C:\temp"
tgtFolder = r"C:\temp"
listSrcFilename = ["file1.txt", "file2.txt", "file3.txt"]
tgtFilename = "merge.txt"

# import section
import os
import shutil

try:
    # Open Target File in write mode
    tgtFilepath = os.path.join(tgtFolder, tgtFilename)
    tgtFile = open(tgtFilepath, "w")
    # Log
    fdmAPI.logInfo("File created: %s" % tgtFilepath)    
    
    # Loop source files to merge
    for srcFilename in listSrcFilename:
    
        # file path
        filepath = os.path.join(srcFolder, srcFilename)          
        # Log
        fdmAPI.logInfo("Merging file: %s" % filepath)                    
        # Open file in read mode
        srcFile = open(filepath, "r")        
        # Copy source file into target
        # 10 MB per writing chunk to avoid big file into memory
        shutil.copyfileobj(srcFile, tgtFile, 1024*1024*10)
        # Add new line char in the target file
        # to avoid issues if source file don't have end of line chars
        tgtFile.write(os.linesep)
        # Close source file
        srcFile.close()
        # Debug
        fdmAPI.logInfo("File merged: %s" % file)
        
    # Close target file
    tgtFile.close()
                    
except (IOError, OSError), err:
    raise RuntimeError("Error concatenating source files: %s", err)

Code snippets for FDMEE can be downloaded from GitHub.

Code Snippet: looping a ResultSet returned by API function

FDMEE provides a set of API functions which return a ResultSet object (getLocationDetails, getBatchDetails, getCategoryList, etc.). These functions are executing a SQL query in the FDMEE database behind the scenes.
Besides, FDMEE API function executeQuery can be used to execute our own SQL queries in the FDMEE database.

The following snippet shows how to loop a ResultSet object, get column values returned, and log details into the FDMEE process log. In my example. I've used a simple query to get internal id of Point-of-View components (Location, Category and Period) for current process id (load id)

How does it work?

Let's have a look!

Looping a ResultSet object returned by FDMEE API Function

'''
 Snippet:       Loop records in ResultSet object (executeQuery)
 Author:        Francisco Amores
 Date:          12/06/2016
 Blog:          http://fishingwithfdmee.blogspot.com
 
 Notes:         This snippet can be pasted in any event/custom script.
                
                Function executeQuery executes SQL queries in the
                FDMEE database
                
 Instructions:  Set log level (global or application settings) to 5 
 Hints:         Use this snippet to execute a SQL query with API
                function executeQuery and loop all records in the 
                ResultSet object returned by API.
                
                This snippet can be also used with other API functions
                returning ResultSet objects
                
 Example:       Get distinct keys for POV components
               
 FDMEE Version: 11.1.2.3 and later
 ----------------------------------------------------------------------
 Change:
 Author:
 Date:
'''

# Import section
import java.sql.SQLException as SQLException
import os # Optional

# SQL Query (Example -> get distinct partitionkey, catkey, and periodkey for specific load id)
sqlQuery = """
            SELECT                
            DISTINCT PARTITIONKEY,
            CATKEY,
            PERIODKEY
            FROM TDATASEG
            WHERE LOADID = ?"""

# List of parameters
# Emtpy list ([]) if no parameters (?) are required -> params = []
loadId = fdmContext["LOADID"]            
params = [loadId]             

# Debug query
fdmAPI.logDebug("SQL Query (params):%s %s (%s)" % (os.linesep, sqlQuery, params))

try:
    # Execute query (returns ResultSet)
    # You can also use any API function returning a ResultSet
    rs = fdmAPI.executeQuery(sqlQuery, params)
    
    # Loop records if resulset has data
    if rs.isBeforeFirst():
        while rs.next():
            # Get column values
            partKey = rs.getString("PARTITIONKEY")
            catKey = rs.getString("CATKEY")
            periodKey = rs.getString("PERIODKEY")
            
            # Write to log (Optional)
            fdmAPI.logDebug("POV Keys: %s, %s, %s" % (partKey, catKey, periodKey))
            
            # Code executed for each record
            # ...  
    else:
        # No records
        fdmAPI.logDebug("No records returned!")    
    
    # Close ResultSet
    fdmAPI.closeResultSet(rs)
    
except (SQLException, Exception), ex:
    # Error message
    errMsg = "Exception: %s" % ex
    fdmAPI.logFatal(errMsg)
    # Optionally raise RunTimeError to stop the process
    # raise RunTimeError(errMsg)

Code snippets for FDMEE can be downloaded from GitHub.

Code Snippet: script code based on EPM environment

We may have some scripts which code differs from one environment to another. Maintaining different code for same script is a risky task.
What about having one single script which you can migrate into the other environments with no risks?
How can I make my code dynamic based on the EPM environment I'm working with?
Let's have a look!

Getting EPM environment based on FDMEE Server hostname

'''
 Snippet:       Get environment running the script based on the FDMEE Server
                hostname
 Author:        Francisco Amores
 Date:          11/11/2016
 Blog:          http://fishingwithfdmee.blogspot.com
 
 Notes:         This snippet can be pasted in any event/custom/import script.
                Output will be logged in the FDMEE process log 
                (...\outbox\logs\)
                
                This script uses socket python module
                
 Instructions:  Set log level (global or application settings) to 5 
                To add new servers, include an entry in the dictionary.
                You can get hostnames by executing command "hostname"
                from the command line in the FDMEE server(s).
 
 Hints:         Use this snippet to avoid having different script codes
                across environments. The right code will be executed
                at runtime based on the FDMEE server running the process
               
 FDMEE Version: 11.1.2.3 and later
 ----------------------------------------------------------------------
 Change:
 Author:
 Date:
'''

# import socket module
import socket

# Dictionaty for FDMEE Servers
# These are sample servers and should be replaced by existing ones
dictFDMEEServers = { 
                     "FDMEE_SERVER_DEV" : "DEV",
                     "FDMEE_SERVER_PROD1": "PROD",
                     "FDMEE_SERVER_PROD2": "PROD"
                   }

# get hostname
hostName = socket.gethostname()

# Assign envFDMEE based on FDMEE Server
try:
    envFDMEE = dictFDMEEServers[hostName]
except KeyError, err:
    errMsg = "FDMEE Server does %s not exist in the dictionary dictFDMEEServers" % hostName
    fdmAPI.showCustomMessage(errMsg)
    raise RuntimeError(errMsg)
    
# Debug
fdmAPI.logDebug("FDMEE Server: %s (%s)" % (hostName, envFDMEE))

# ****************************************************************
# Specific Code for Development
# ****************************************************************
if envFDMEE == "DEV":
    # Debug
    fdmAPI.logDebug("Code for %s" % envFDMEE)
    
    # your code here

# ****************************************************************
# Specific Code for Production
# ****************************************************************
if envFDMEE == "PROD":
    # Debug
    fdmAPI.logDebug("Code for %s" % envFDMEE)
    
    # your code here 

# ****************************************************************
# Specific Code for Environment XXX
# ****************************************************************
if envFDMEE == "XXX":
    # Debug
    fdmAPI.logDebug("Code for %s" % envFDMEE)
    
    # your code here  


# ****************************************************************
# Code for all environments
# ****************************************************************

# your code here

Code snippets for FDMEE can be downloaded from GitHub.

Code Snippet: logging fdmContext values

Object fdmContext is the dictionary FDMEE uses to store context information such as Location Name, Import Format Name, Process Id, etc. Most of the values stored in fdmContext are specific for the current process being executed. Others are global to all processes.

If you want to learn more about dictionaries in Python/Jython, please visit this tutorial.

Logging all fdmContext properties (loop sorted dictionary)

Not sure which are the values you can get from fdmContext at any event script?
Simply include this snippet into your event script and see results in the FDMEE process log.

'''
 Snippet:       Log content of fdmContext object
 Author:        Francisco Amores
 Date:          11/11/2016
 Blog:          http://fishingwithfdmee.blogspot.com
 
 Notes:         This snippet can be pasted in any event script.
                Content of fdmContext object will be logged in the
                FDMEE process log (...\outbox\logs\)
 Instructions:  Set log level (global or application settings) to 5 
 Hints:         Use this snippet to see different context values
                at any workflow step.
               
 FDMEE Version: 11.1.2.3 and later
 ----------------------------------------------------------------------
 Change:
 Author:
 Date:
'''

# initialize 
logLines = ""

# loop all fdmContext keys (sorted)
for key in sorted(fdmContext):
# get key value
value = fdmContext[key]
# build log line (property name: property value)
logLines += "%s: %s\n" % (key, value)

# write line to log (Debug)
if logLines:
fdmAPI.logDebug("Content of fdmContext: \n%s" % logLines)
else:
fdmAPI.logWarn("Nothing to log from fdmContext")

Logging all fdmContext properties (list comprehension)

First, make it working. Then, make it Jythonic. Try to simplify your code and make it more readable.
We can also use list comprehension to build lists dynamically:

'''
 Snippet:       Log content of fdmContext object
 Author:        Francisco Amores
 Date:          11/11/2016
 Blog:          http://fishingwithfdmee.blogspot.com
 
 Notes:         This snippet can be pasted in any event script.
                Content of fdmContext object will be logged in the
                FDMEE process log (...\outbox\logs\)
                
                This script uses list comprehension builder
                
 Instructions:  Set log level (global or application settings) to 5 
 Hints:         Use this snippet to see different context values
                at any workflow step.
               
 FDMEE Version: 11.1.2.3 and later
 ----------------------------------------------------------------------
 Change:
 Author:
 Date:
'''

# initialize 
logLines = ""

# List comprehension
# Define list with pairs "Key: Value" from sorted fdmContext
list = ["%s: %s" % (key, fdmContext[key]) for key in sorted(fdmContext)]
# List is then joined with "\n" character (newline)
logLines = "\n".join(list)

# write line to log (Debug)
if logLines:
fdmAPI.logDebug("Content of fdmContext: \n%s" % logLines)
else:
fdmAPI.logWarn("Nothing to log from fdmContext")

Code snippets for FDMEE can be downloaded from GitHub.