Under the OWB engine

My, it has been a long time since I have posted shame on me. Today we are going to show you a little under the engine of OWB. More specifically, some queries to extract details of a map from the repository. First, I make no guarantees that the following is 100% correct. At least these queries may help you get a foothold on how OWB is storing maps, projects, and properties.

---================================================================
--- To get the project ID
-------------------------------------------------------------------
select I_1
FROM "OWB_REPOSOWNER"."CMPFCOCLASSES"
where S2_1 = 'CMPWBProject'
and S2_3 = 'DSS_' 'FIN'

---RESULT---------------------------------------------------------
8184190
---================================================================


---================================================================
--- To get the install oracle folder in the project
--------------------------------------------------------------------------------

select * --I_1
FROM "OWB_REPOSOWNER"."CMPFCOCLASSES"
WHERE R_19 = 8184190
and S2_1 = 'CMPWBDataWarehouse'
and S1_8 = 'Production'

---RESULT---------------------------------------------------------
8344258

---NOTE-----------------------------------------------------------
--- Other options
--- CMPWBDataWarehouse
--- CMPProcessInstalledModule process flow modules
--- CMPWBFileInstalledModule flat file folder
---================================================================


---================================================================
--- To get the expected files list
-------------------------------------------------------------------
select S3_4
FROM "OWB_REPOSOWNER"."CMPSCOPRPCLASSES"
where R_11 IN
(select I_1
FROM "OWB_REPOSOWNER"."CMPFCOCLASSES"
where r_17 = 8344258
and s2_1 = 'CMPMap'
)
and S4_1 = '8i.MAPPINGS.SQLLOADERSTEP.DATAFILE.IDENTIFICATION.DATAFILENAME'

---RESULT---------------------------------------------------------

f_recip_alt_id.dat
f_clm_hdr.dat
f_hdr.dat
f_note.dat
f_pybl.dat
f_pybl_recv.dat
f_recv.dat
---================================================================


---================================================================
--- To get the filename and stored location name
-------------------------------------------------------------------
SELECT a.S3_4,b.S3_4 FROM
(select *
FROM "OWB_REPOSOWNER"."CMPSCOPRPCLASSES"
where R_11 IN
(select I_1
FROM "OWB_REPOSOWNER"."CMPFCOCLASSES"
where r_17 = 8344258 and s2_1 = 'CMPMap'
)
and S4_1 = '8i.MAPPINGS.SQLLOADERSTEP.DATAFILE.IDENTIFICATION.DATAFILENAME'
) A
INNER JOIN
(select *
FROM "OWB_REPOSOWNER"."CMPSCOPRPCLASSES"
where R_11 IN
(select I_1
FROM "OWB_REPOSOWNER"."CMPFCOCLASSES"
where r_17 = 8344258 and s2_1 = 'CMPMap'
)
and S4_1 = '8i.MAPPINGS.SQLLOADERSTEP.DATAFILE.IDENTIFICATION.DATAFILELOC'
) B
ON A.R_11 = b.R_11

---RESULT---------------------------------------------------------

f_recip_alt_id.dat 60383E500D6341A6AF8DFD4A03B78F06:DSS_FIN_DATA_SOURCE
f_clm_hdr.dat 60383E500D6341A6AF8DFD4A03B78F06:DSS_FIN_DATA_SOURCE
f_hdr.dat 60383E500D6341A6AF8DFD4A03B78F06:DSS_FIN_DATA_SOURCE
f_note.dat 60383E500D6341A6AF8DFD4A03B78F06:DSS_FIN_DATA_SOURCE
f_pybl.dat 60383E500D6341A6AF8DFD4A03B78F06:DSS_FIN_DATA_SOURCE
f_pybl_recv.dat 60383E500D6341A6AF8DFD4A03B78F06:DSS_FIN_DATA_SOURCE
f_recv.dat 60383E500D6341A6AF8DFD4A03B78F06:DSS_FIN_DATA_SOURCE

---================================================================

---================================================================
--- To get the filename and directory
-------------------------------------------------------------------
SELECT a.S3_4, E.S3_4

FROM
(select *
FROM "OWB_REPOSOWNER"."CMPSCOPRPCLASSES"
where R_11 IN
(select I_1
FROM "OWB_REPOSOWNER"."CMPFCOCLASSES"
where r_17 = 8344258 and s2_1 = 'CMPMap'
)
and S4_1 = '8i.MAPPINGS.SQLLOADERSTEP.DATAFILE.IDENTIFICATION.DATAFILENAME'
) A
INNER JOIN
(select *
FROM "OWB_REPOSOWNER"."CMPSCOPRPCLASSES"
where R_11 IN
(select I_1
FROM "OWB_REPOSOWNER"."CMPFCOCLASSES"
where r_17 = 8344258 and s2_1 = 'CMPMap'
)
and S4_1 = '8i.MAPPINGS.SQLLOADERSTEP.DATAFILE.IDENTIFICATION.DATAFILELOC'
) B
ON A.R_11 = b.R_11
INNER JOIN
"OWB_REPOSOWNER".CMPFCOCLASSES C
ON C.S2_5 ':' C.S2_3 = B.S3_4
INNER JOIN "OWB_REPOSOWNER"."CMPSCOPRPCLASSES" E
ON E.R_15 = C.I_1

---RESULT---------------------------------------------------------

f_recip_alt_id.dat J:\FIN\
f_clm_hdr.dat J:\FIN\
f_hdr.dat J:\FIN\
f_note.dat J:\FIN\
f_pybl.dat J:\FIN\
f_pybl_recv.dat J:\FIN\
f_recv.dat J:\FIN\
---================================================================


---================================================================
--- To get the database connection details. This is good to verify that the map
--- will run into the correct user schema and the correct oracle instance.
-------------------------------------------------------------------

select A "table_name"
, MAX(DECODE(B,'CMPLocation_Host',C)) as "HOST"
, MAX(DECODE(B,'CMPLocation_ConnectAsUser', C)) as "USER_SCHEMA"
, MAX(DECODE(B,'CMPLocation_ServiceName',C)) as "SERVICE_NAME"
from
(

SELECT a.S3_4 a,E.S4_1 b, E.S3_4 c
FROM
(select *
FROM "OWB_REPOSOWNER"."CMPSCOPRPCLASSES"
where R_11 IN
(select I_1
FROM "OWB_REPOSOWNER"."CMPFCOCLASSES"
where r_17 = 8344258 and s2_1 = 'CMPMap'
)
and S4_1 = 'DATAENTITY.PARENT_OBJECT_NAME'
) A
INNER JOIN
(select *
FROM "OWB_REPOSOWNER"."CMPSCOPRPCLASSES"
where R_11 IN
(select I_1
FROM "OWB_REPOSOWNER"."CMPFCOCLASSES"
where r_17 = 8344258 and s2_1 = 'CMPMap'
)
and S4_1 = '8i.MAPPING.ENTITY.LOCATION'
) B
ON A.R_11 = b.R_11
INNER JOIN
"OWB_REPOSOWNER".CMPFCOCLASSES C
ON C.S2_5 ':' C.S2_3 = B.S3_4
INNER JOIN "OWB_REPOSOWNER"."CMPSCOPRPCLASSES" E
ON E.R_15 = C.I_1

) g

group by A
order by A

---RESULT---------------------------------------------------------

table_name HOST USER_SCHEMA SERVICE_NAME
-------------- ------- -------------- ----------------
FIN_BEN_ALT_ID raca ODS0_FIN dss.jp.db.com
FIN_CLM_HDR raca ODS1_FIN dss.jp.db.com
FIN_HDR raca ODS1_NET dss.jp.db.com
FIN_NOTE raca ODS1_REC dss.JP.db.COM
FIN_PYBL racd ODS2 dss.jp.db.com
FIN_PYBL_RECV racd ODS2 dss.jp.db.com
FIN_RECV racd ODS2 dss.jp.db.com

---================================================================

Job Monitor

This is more of a tip, but instead of using the control center manager to watch the state of an executing map or process flow, you can use the job monitor.

Query for the audit Log file

You might be asking yourself “Man I would love to query out the log file instead of opening the file in a directory.” Well, today you get your wish.

After you run a map, get the AUDIT_EXECUTION_ID.(you can get that in the job monitor or the control center manager). Now open SQL Developer and switch to the user schema where your repository is located.


SELECT * FROM WB_RT_AUDIT_FILES WHERE AUDIT_EXECUTION_ID = 2895504

SQL*Loader: Release 10.2.0.1.0 - Production on Thu Jul 10 13:08:27 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Control File: J:\XXXXXXXXXX.ctlCharacter Set WE8MSWIN1252 specified for all input.
Data File: J:\XXXXXXXXXX.txt Bad File: c:\XXXXXXXXXX.bad Discard File: c:\XXXXXXXXXX.dsc (Allow 0 discards)
Number to load: ALLNumber to skip: 0Errors allowed: 50Bind array: 200 rows, maximum of 50000 bytesContinuation: Concatenate every 1 physical recordsPath used: ConventionalSilent options: FEEDBACKTable "TMP_XXXXXXXXXX", loaded from every logical record.Insert option in effect for this table: APPENDTRAILING NULLCOLS option in effect
.
.
.


Cheers

Danny

Oracle IIF

Hello All,

One of the native functions I miss a lot from SQL Server is the IIF. (http://msdn.microsoft.com/en-us/library/27ydhh0d(VS.71).aspx). So I made one for myself for Oracle. Simple but very useful.

Enjoy,
Danny



DECLARE
v_Return VARCHAR2(200);
BEGIN

v_Return := IIF(
1=2,
'YES',
'NO'
);
DBMS_OUTPUT.PUT_LINE('v_Return = ' v_Return);
END;


create or replace
FUNCTION ODS_ETL.IIF
( param1 IN BOOLEAN
, param2 IN VARCHAR2
, param3 IN VARCHAR2
) RETURN VARCHAR2 AS
BEGIN
IF param1
THEN
RETURN param2;
ELSE
RETURN param3;
END IF;
END IIF;

What is the last day of last month and more

To get the first day of the month last month use this:
select TRUNC(ADD_MONTHS(SYSDATE, - 1), 'MONTH')FROM DUAL

To get the last day of the month, use this:
select LAST_DAY(ADD_MONTHS(SYSDATE, - 1))FROM DUAL

What is the name of the Schedule that is running a Process Flow

One of my main irks with Oracle Warehouse Builder (OWB)

One of my main irks with Oracle Warehouse Builder (OWB) is the lame messages you receive. I mean seriously, they can leave you with a sense of what hell does that mean and frustration to the point of pulling your hair out.

My case in point today was I had a function with a parameter that returns back a number that is to be used in a wait activity (figure 1). Sound simple enough doesn’t? Wrong. So you can see in figure2 the parameter for the function. And in Figure 3 you can see the binding of wait time to this return parameter.

figure 1

figure 2




















figure 3

Validation of the map was ok. I then deployed the map but that failed. The reason?

GEAP_WRK CreateError RPE-02040: Internal error: P_NWAIT_BETWEEN_EXTRACTS cannot be converted to a constant value. Please correct the value. If the problem persists then please contact Oracle Support with the stack trace and details on how to reproduce it.

See what I mean, this helps no one understand what the problem is or how to fix it. Fortunately I have run across this problem before so I pretty much know what to do. In this case you need to create a process flow variable and bind the parameter to this value. Then you need to bind the wait time property to this variable.