Creating a User_Rebuild stored procedure
You can create your own user datamart to perform site
specific SQL tasks over the Infomart database.
-
Create a User_Rebuild stored procedure:
-
If you are using SQL Server, use this template:
if exists (select name from sysobjects where uid in (select uid from sysusers where name = 'infodbo') and name = 'User_Rebuild' and type='P') Drop procedure [infodbo].[User_Rebuild] GO Create procedure [infodbo].[User_Rebuild] @job int = 0 as set nocount on declare @error_var int exec [infodbo].[log_entry] @job,'I',0,'Vicval_10','Start User SQL.' -- ******* Start User area for SQL statement Print 'Start User SQL...' -- INSERT YOUR SQL STATEMENTS HERE. AFTER EACH STATEMENT YOU SHOULD CHECK THE STATUS OF @@ERROR... SET @error_var = @@ERROR if @error_var <> 0 exec [infodbo].[log_entry] @job,'E',@error_var,'User_Rebuild','Error occurred.' -- IF YOU WISH TO CALL ANOTHER USER STORED PROCEDURE THE USE THE STATEMENT BELOW... -- 1. Change the 'USER_000' value to your SP name. -- 2. Uncomment the line. -- exec [infodbo].[rebuild_procedure] @job_id, 'USER_000' Print 'End User SQL.' -- ******* End User area for SQL statement exec [infodbo].[log_entry] @job,'I',0,'Vicval_10','End User SQL.' GO
-
If you are using Oracle, use this template:
CREATE OR REPLACE PROCEDURE User_Rebuild ( p_JobId INTEGER, p_Status OUT INTEGER ) -- Populate Animals IS v_Procedure VARCHAR2(20) := 'User_Rebuild'; v_Parm_Value VARCHAR2(80); v_Sysdate DATE := SYSDATE; BEGIN -- 0010 -- Set Status to ERROR, reset on completion p_Status := -1; -- Checkpoint 0000 SYSTEM.LOG(p_JobId,'C',0,v_Procedure,'CP0000'); -- INSERT YOUR SQL STATEMENT HERE. -- Checkpoint 0010 SYSTEM.LOG(p_JobId,'C',0,v_Procedure,'CP0010'); -- INSERT YOUR NEXT SQL STATEMENT HERE. -- ... -- Set Completion Status to Good p_Status := 1; -- error check EXCEPTION -- log error WHEN OTHERS THEN COMMIT; SYSTEM.LOG(p_JobId,'E',SQLCODE,v_Procedure,NULL); END;
-
If you are using SQL Server, use this template:
- Modify the USER_REBUILD.SQL script.
-
Execute the script in a Pathway session when connected to the Infomart
database:
- On the sidebar, select System Administration > Infomart > Rebuilding > Datamart Maintenance.
- Check the last record in the Datamart grid, which is USER datamart.
- Select the Rebuild check box for this record.
- Click Save.
- Select System Administration > Infomart > Rebuilding > Rebuild Infomart.
- Clear the Rebuild check boxes for datamarts that are not to be run.
- Select the USER datamart, then click Submit.
Note: The customer is responsible for developing and maintaining the script. Infor Professional Services can assist as required.