| Terminology | Oracle | SQL Server |
| schema | database | |
| service name | database name | |
| System ID (SID) | database name | |
| Storage | block | page |
| Extent | user-defined | fixed at 8 pages |
| Storage management pages (SMP) | dictionary or local | local only |
| Metadata | data dictionary | SYS database |
| Recursive SQL | connect by clause | HierarchyID data type |
| Language | PL/SQL | T-SQL |
DBA_DIRECTORIES describes all directory objects in the database. Its columns are the same as those in "ALL_DIRECTORIES".Found something interesting last week @ work. This was a failure we had due to data overflow. We were trying to load data from source of higher precision into the target of same data type but with lesser precision. We had a source column of Decimal(17,3). Before we proceed, we must all be aware that 17 here refers to the precision length (i.e. 14 digits before decimal + 3 digits after decimal). And the target was set of a size or precision Numeric(15,5) means 10 digits before and 5 digits after decimal.
This was all working good until all our source data was within the precision of (15,5), and finally when the size outlined this there came a data overflow error while running the Informatica session. so all we had to do was change the precision at the data base level as from Numeric(15,5) to Numeric(17,3). Then again the session failed, and it was right as we are yet to change the same in the Informatica Target transformation.
Now a change done on Target in informatica to match with the table in SQL Server (in our case the target table here). On running the session it all succeeded. But there is something we have to wonder on!! "The precision were not changed in all other transformation in the mapping!! still the session made it through with no truncate on the source data value."
Well this was possible with one property set on your session level, enable high precision on the properties tab of the session. In case of Decimal values the Integration services handles the values in a different way as is explained in the
Informatica help:
"To ensure precision of up to 28 digits, use the Decimal datatype and enable high precision in the session properties. When you run a session with high precision, the Integration Service processes decimal values as Decimal. Precision loss does not occur in a calculation unless the result produces a value with precision greater than 28 digits. In this case, the Integration Service stores the result as a double."
For example, you have a mapping with Decimal (17,3) that passes the number 400120303049 and we have to convert from here to Numeric(15,5). If the session does not run with high precision, the Integration Service converts the decimal value to Numeric and passes 4001203030 to the next transformation.
To sum up here, the integration services allows the precision of the maximum allowed (say for decimal as 28) to pass through without any truncate on the result one and only if the high precision property is set on the session Property.
PIVOT feature seems to have recieved a lot of bloggers attention, but I haven't read much about UNPIVOT. Using UNPIVOT with one column is pretty straight forward and easy, just a small example (I use Oracle Database 11.1.0.7 Enterprise Edition for all the examples here):
1 2 3 4 5 6 7 | create table unpivot_ex1 ( id number primary key, jan number, feb number, mar number, apr number); |
Fill it up with test data:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | insert into unpivot_ex1 (id, jan, feb, mar, apr) values (1, 1, 2, null, 4);insert into unpivot_ex1 (id, jan, feb, mar, apr) values (2, 5, 6, null, 8);insert into unpivot_ex1 (id, jan, feb, mar, apr) values (3, 9, 10, null, null);commit;SELECT * FROM unpivot_ex1; ID JAN FEB MAR APR---------- ---------- ---------- ---------- ---------- 1 1 2 4 2 5 6 8 3 9 10 |
Now, if you want to select JAN, FEB, MAR, APR columns as separate rows, then its possible to use the simplest UNPIVOT clause:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 | SELECT * FROM unpivot_ex1 UNPIVOT INCLUDE NULLS (monthly_value FOR month IN (jan, feb, mar, apr)); ID MON MONTHLY_VALUE---------- --- ------------- 1 JAN 1 1 FEB 2 1 MAR 1 APR 4 2 JAN 5 2 FEB 6 2 MAR 2 APR 8 3 JAN 9 3 FEB 10 3 MAR 3 APR12 rows selected. |
Now, lets take a more interesting case. What if some of the columns are combined into logical groups and you want to preserve them in one row? For example:
1 2 3 4 5 6 7 8 9 | create table unpivot_ex2 ( id number primary key, jan_income number, jan_expense number, feb_income number, feb_expense number, mar_income number, mar_expense number); |
For every month there is income and expense column.
1 2 3 4 5 6 7 8 9 10 | insert into unpivot_ex2 values (1, 1, 1, 2, 3, 5, 2);insert into unpivot_ex2 values (2, 5, 6, 4, 4, null, null);commit;SELECT * FROM unpivot_ex2; ID JAN_INCOME JAN_EXPENSE FEB_INCOME FEB_EXPENSE MAR_INCOME MAR_EXPENSE---------- ---------- ----------- ---------- ----------- ---------- ----------- 1 1 1 2 3 5 2 2 5 6 4 4 |
UNPIVOT allows to create column groups, like this:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | SELECT * FROM unpivot_ex2 UNPIVOT INCLUDE NULLS ((income, expense) FOR month IN ( (jan_income, jan_expense), (feb_income, feb_expense), (mar_income, mar_expense))); ID MONTH INCOME EXPENSE---------- ---------------------- ---------- ---------- 1 JAN_INCOME_JAN_EXPENSE 1 1 1 FEB_INCOME_FEB_EXPENSE 2 3 1 MAR_INCOME_MAR_EXPENSE 5 2 2 JAN_INCOME_JAN_EXPENSE 5 6 2 FEB_INCOME_FEB_EXPENSE 4 4 2 MAR_INCOME_MAR_EXPENSE6 rows selected. |
