Product:
Cognos Controller 10.4 local
Microsoft Windows 2016 server
Oracle Database 12c
Problem:
When try to run the store procedure to load data into Cognos Controller, with use of staging table, you get a error.
Error in SQL developer:
Error report –
ORA-01861: literal does not match format string
ORA-06512: at line 5
Solution:
Include formatting information in the call to the store procedure for oracle.
Inside Oracle SQL developer enter this command to execute:
xx number;
begin
— Call the function
xx := FRANGO.usp_triggerimportbatchjobs(‘1a’,’EUROIMSP’,’D’,”,’ADM’,1,to_date(‘2019-07-03′ ,’yyyy-mm-dd’));
end;
Replace FRANGO with the schema you want to update.
The 1 say it will run now. Replace 2019-07-03 with today date.
EUROIMSP is the name of the controller import specification, that should be used during the process.
Background:
Inside the cognos controller database are some tables that you use to load data into Cognos Controller.
One table is XSTAGEFACT, that is used to load data into Cognos Controller.
Before loading data into the table truncate it with this command:
TRUNCATE TABLE FRANGO.XSTAGEFACT;
(replace frango with the schema name of your database)
Prepare a csv/xls file with the same columns as in the table.
To make a easy import from file, ensure the first rows name exactly match the columns names, e.g. ST_PERIOD
Column ST_ID should be a alphanumeric identifier, in our example we use “1a”.
Column BATCH_ID should be blank, as here will Cognos Controller add the batch work number.
Use the wizard in SQL Devloper to import your data from file to the xstagefact table.
https://www.thatjeffsmith.com/archive/2012/04/how-to-import-from-excel-to-oracle-with-sql-developer/
Ensure you have “import specification” that works with a csv file first, then you need to update that (make a copy) to be ready for use with Controller Staging Tables. Two of the things you need to do, to make it work for staging tables are;
Change the general settings to “Controller Staging Table”
Go through the fields and set the to origin to “Controller Staging Table” and operation to “None”.
Save the new Controller Import Specification. https://www.ibm.com/support/knowledgecenter/en/SS9S6B_10.4.0/com.ibm.swg.ba.cognos.ctrl_web_ug.10.4.0.doc/t_defining_import_spec_web.html
Then you need to start the store procedure, for testing direct from SQL developer.
xx number;
begin
— Call the function
xx := FRANGO.usp_triggerimportbatchjobs(‘1a’,’EUROIMSP’,’D’,”,’ADM’,1,to_date(‘2019-07-03′ ,’yyyy-mm-dd’));
end;
The parameters for the usp_triggerimportbatchjobs is:
@pImpId, this is the value in the st_id column identifying the lines are to be imported.
@pImpSpec, this is the NAME of the import specification that is used for this import. This must exist in Cognos Controller.
@pImpSpecType, this is the code to identify the information that is to be loaded, it could be any of the following values: D=Data A=Account R=Currency Rates C=Company 1=Dim 1 2=Dim 2 3=Dim 3 4=Dim 4 . D means values will be read from xstagefact table.
@ImpSepcParams, this is the list of any additional parameters that need to be sent to the import spec, we leave it blank.
@CtrlUser, this is the user ID in Controller to be used for the import, in most cases ADM, even when you use CAM security.
@SchedType, this is the parameter that decides to import immediately or schedule for later date, it can include the following values: 0= Hold 1=Immediately 2=One time only. We use 1.
@ExecTime, this is the date and time for scheduled import to be carried out. The format should match the database server date format.
If you want to test run the SP again, with same data, then you need to clear the BATCH_ID column first with the command:
UPDATE FRANGO.XSTAGEFACT SET BATCH_ID = NULL;
For other database, you can use command like this to start the controller store procedure:
On the DB2 server, launch ” DB2 Command Window – Administrator”
2. Type the following command:
db2 connect to <database_name> user <username> using <password>
NOTE: <username> must be the exact same user (for example ‘fastnet’) that Controller is configured to use. In other words, the same user that is specified inside the ‘database connection’ inside Controller Configuration.
3. Type a command similar to the following:
db2 call usp_triggerimportbatchjobs(‘110′,’#ST_DATA’,’D’,”,’ADM’,1,to_date(‘2007-11-01’));
On Microsoft SQL server:
Execute the stored procedure via the following SQL Script:
EXECUTE db_owner.usp_triggerimportbatchjobs ‘<parameter1>’, ‘<parameter2>’, ….
Next step, is to make the loading of data automatic with some tool. Like Microsoft SSIS, IBM Data Stage or other tool.
Then you from that tool schedule the start of the store procedure to active the batch load of data into Controller database.
More information:
https://www-01.ibm.com/support/docview.wss?uid=swg21458934
https://www-01.ibm.com/support/docview.wss?uid=swg21370812
https://www-01.ibm.com/support/docview.wss?uid=swg21374718
https://www.ibm.com/support/knowledgecenter/en/SS9S6B_10.4.1/com.ibm.swg.ba.cognos.ctrl_ug.doc/c_ctrl_ug_expimp_importingdatafromanexternalapplication.html#ctrl_ug_expimp_ImportingDatafromanExternalApplication
https://en.wikipedia.org/wiki/SQL_Server_Integration_Services
