Custom function to get database name from a datastore in DS
Getting function name from a datastore could be useful if you have configured more than one datastores in your project. This helps avoiding code change while migrating objects from one environment to another during the code promotion.
Below is step by step procedure to create custom function to get the database name from a datastore and call it in a Batch Job.
1) Go to your Local Object Library and choose Custom Functions then right click on Custom Function and select New
2) Enter name of the function as ‘CF_DATABASE_NAME’
3) Enter the below line of code inside the editor.
Click on the above image to zoom it.
Then declare an input parameter named $P_Datastore of length varchar(64). Then Validate the function and if no error found then Save it.
4) Create a global variable at Job level in any of the test batch job you have created and name this global variable as $G_Database of length varchar(64)
5) Call this function in one script of your batch job and use this Global variable wherever required in your Job.
You can call this function as under in script. You simply need to pass the name of your datastore in single quotes.
Example of a practical use:
sql(‘CONV_DS’,’ TRUNCATE TABLE ||$G_DATABASE||’.ADMIN.CONV_CUSTOMER_ADDRESS’);
So the above code will read database name at run time from global variable and truncate records before it loads in the Job. This code can be used in any environment when you promote your code else if database name is hard coded then you will end up updating the code in every new environment.
In Miscellaneous function we have inbuilt function called (datastore_field_value) which will give the database name from a datastore.
e.g. datastore_field_value ('DS_LOCAL','SQL_SERVER_DATABASE')
Result : DS_STAGING
where DS_LOCAL is the datastore name
DS_STAGING is the Database name ..
thanks Ravi, but problem is here is that you have to pass the database type (i.e. SQL_Server_Database) which means you should know it in advance. But what this custom function does is that it extract the database name based on the datastore without knowing what is the type of database. Type could be sql server, oracle, netteza or anything. I hope you got the point. 🙂
So this function is independent of the database type. Different environment may have setup different database for the same datastore so this function would be useful in all such cases.
However, I was reading through the reference guide and found that we have another in-built function db_database_name which returns the database name of the datastore configuration in use at runtime.