#DateWhereItemComment
2009 Apr 2012 @ 10:02 PDTMarcio MouraLet's talk about Surrogate Key Generationthe key table is not a type 2 table. The target tables are type 2 tables where the SK is defined. But the PK on the target tables it is usually the SK plus effective timestamp. The SK will be the s...
1930 Mar 2012 @ 01:12 PDTMarcio MouraLet's talk about Surrogate Key GenerationThe process will not generate a new key for an existing (SRC_SYS_UNQ_KEY_TX, SRC_SYS_CD) on the key table and the type 2 change will use the same surrogate key to apply the change on the target table.
1826 Mar 2012 @ 06:34 PDTMarcio MouraLet's talk about Surrogate Key GenerationI would set an active indicator on the key table to flag the expired surrogate keys. I would also change the logic on the process to include maximum surrogate key value to the equation and a resta...
1720 Mar 2012 @ 04:02 PDTMarcio MouraLet's talk about Surrogate Key GenerationI don't agree that CSUM() function is slow. I used the CSUM function to generate millions of surrogate keys in a couple of seconds. The bottle neck can be the key table and not the CSUM function.
1608 Dec 2011 @ 11:56 PSTMarcio MouraLet's talk about Aggregate Join Indexes (AJI)There is no option to disable AJI maintenance unless you drop the AJI before loading and recreate after the load is done. Another option is to create a report/semantic layer and have the AJI defin...
1502 Dec 2011 @ 02:20 PSTMarcio MouraLet's talk about Aggregate Join Indexes (AJI)Here is the query to generate AJI's Hits Report from the historical DBQLObjTbl table. LOCK ROW FOR ACCESS SELECT CASE WHEN LogDate BETWEEN DATE '2010-01-01' AND DATE '2010-01-31' THEN '2010-...
1402 Dec 2011 @ 02:06 PSTMarcio MouraLet's talk about Aggregate Join Indexes (AJI)I am sorry but I can't provide sample queries. But the majority of the queries are coming from COGNOS.
1331 Jan 2011 @ 04:08 PSTMarcio MouraLet's talk about Multilevel Partitioned Primary Indexes (MLPPI)it is the same as loading into an empty table. It does not create a TJ.
1217 Jan 2011 @ 11:18 PSTMarcio MouraLet's talk about Multilevel Partitioned Primary Indexes (MLPPI)The benefits will depend on each case. The MLPPI provides more granularity for a partition and this can improve the report and load processes. My email is mtmoura@us.ibm.com
1106 Dec 2010 @ 01:58 PSTMarcio MouraWhen is the right time to refresh statistics? - Part IIt usually takes less five minutes to refresh stats on DBC tables.
1006 Dec 2010 @ 01:56 PSTMarcio MouraWhen is the right time to refresh statistics? - Part IIIt is possible to have changes on the overall size of a table due to updates because of cylinder splits and the process will recollect stats after a compression being applied and the savings is mor...
906 Dec 2010 @ 01:46 PSTMarcio MouraLet's talk about Stored Proceduresthe SP needs to be defined under an User instead of a database for you to be able to use the dynamic SQL (DBC.SysExecSQL).
806 Dec 2010 @ 01:32 PSTMarcio MouraLet's talk about Multilevel Partitioned Primary Indexes (MLPPI)There is no TJ when using a MLOAD but it will be inserting into an empty for every source system. But the process will still have to maintain the NUSI's.
711 Jun 2010 @ 10:41 PDTMarcio MouraLet's talk about Multilevel Partitioned Primary Indexes (MLPPI)the process is loading into an empty partition on the table and on the AJI's every time and also deleting an entire partition on the table and on the AJI's. Therefore, the impact of a rollback is m...
617 May 2010 @ 01:27 PDTMarcio MouraLet's talk about Multilevel Partitioned Primary Indexes (MLPPI)It will depend on the overall system utilization while the load is running and also the volume of the data. It will also depend on how the reporting views are setup. Is dirty reads are allowed or n...
517 May 2010 @ 01:13 PDTMarcio MouraLet's talk about Stored ProceduresSorry but I can't provide you the source of SP_INDEX_STATS. The outside exception handler above shows how to check the error log table after the SP call. This is the step that you suppose to do aft...
415 May 2010 @ 07:19 PDTMarcio MouraLet's talk about Stored ProceduresThe parallelism of a SP really depends in terms of how the it is designed/implemented. For example, I designed a SP for Change Data Capture (CDC) to process all records from a staging table in para...
303 May 2010 @ 10:02 PDTMarcio MouraLet's talk about Stored ProceduresSorry but I don't have experience with External stored procedures in JAVA.
204 Feb 2010 @ 10:17 PSTMarcio MouraWhen is the right time to refresh statistics? - Part IIHi Carrie, it usually takes less than a minute to refresh the dictionary stats in my production environment and I am refreshing at the table level. Because of that I refresh on a daily basis. So...
106 Jan 2010 @ 04:41 PSTMarcio MouraWhen is the right time to refresh statistics? - Part IAbraham, the best way to identify stale columns is to turn on the collection on DBQL Object table and run a monthly report on the column usage level by table/database.