Skip navigation.

Feed aggregator

Getting Started with Impala Interactive SQL for Apache Hadoop by John Russell; O'Reilly Media

Surachart Opun - Sat, 2014-10-25 11:52
Impala is open source and a query engine that runs on Apache Hadoop. With Impala, you can query data, whether stored in HDFS or Apache HBase – including SELECT, JOIN, and aggregate functions – in real time. If you are looking for a book getting start with it - Getting Started with Impala Interactive SQL for Apache Hadoop by John Russell (@max_webster). Assist readers to write, tune, and port SQL queries and other statements for a Big Data environment, using Impala. The SQL examples in this book start from a simple base for easy comprehension, then build toward best practices that demonstrate high performance and scalability. For readers, you can download QuickStart VMs and install. After that, you can use it with examples in a book.
In a book, it doesn't assist readers to install Impala or how to solve the issue from installation or configuration. It has 5 chapters and not much for the number of pages, but enough to guide how to use Impala (Interactive SQL) and has good examples. With chapter 5 - Tutorials and Deep Dives, that it's highlight in a book and the example in a chapter that is very useful.
Free Sampler.

This book assists readers.
  • Learn how Impala integrates with a wide range of Hadoop components
  • Attain high performance and scalability for huge data sets on production clusters
  • Explore common developer tasks, such as porting code to Impala and optimizing performance
  • Use tutorials for working with billion-row tables, date- and time-based values, and other techniques
  • Learn how to transition from rigid schemas to a flexible model that evolves as needs change
  • Take a deep dive into joins and the roles of statistics
[test01:21000] > select "Surachart Opun" Name,  NOW() ;
Query: select "Surachart Opun" Name,  NOW()
+----------------+-------------------------------+
| name           | now()                         |
+----------------+-------------------------------+
| Surachart Opun | 2014-10-25 23:34:03.217635000 |
+----------------+-------------------------------+
Returned 1 row(s) in 0.14sBook: Getting Started with Impala Interactive SQL for Apache HadoopAuthor: John Russell (@max_webster)Written By: Surachart Opun http://surachartopun.com
Categories: DBA Blogs

Open World Recap

PeopleSoft Technology Blog - Fri, 2014-10-24 16:29

Oracle’s Open World conference was completed in early October.  It was one of the best ever for PeopleSoft.  We introduced a lot of new and important features like the new Fluid User Experience and the PeopleSoft Update Manager (and much more).  The response from customers was overwhelmingly positive.  Our sessions were well attended, and the demo grounds were consistently busy with inquisitive customers.

PeopleSoft offered informative general sessions like Paco Aubrejuan’s  “PeopleSoft Strategy and Roadmap—Modern Solutions Delivered with Ease” .  In that session, Oracle’s Senior Vice President and General Manager of PeopleSoft Development addressed a standing room only crowd, and described how Oracle’s continued investment in PeopleSoft is delivering key innovations that address our customer’s greatest challenges:

  • Delivering a robust solution that is intuitive and easy to use – regardless of device (mobile device or desktop workstation)
  • Ensuring that new PeopleSoft capabilities are delivered on a continuous basis and are easy to adopt at a low cost, without the need to upgrade.

Paco shared how the investments in the new PeopleSoft Fluid User Interface and the new continuous PeopleSoft Delivery Model address these challenges.

PeopleSoft Application sessions were also well attended.  Our applications teams provided greater depth and specificity, describing how PeopleSoft applications are taking advantage of the latest PeopleTools technology, particularly in the areas of the Fluid UI and PeopleSoft Update Manager and the new delivery model.  

Our PeopleTools Tech sessions were packed with curious, enthusiastic customers.  Here are some highlights:

The PeopleTools team delivered several sessions focused on the Fluid User Interface, covering different aspects and for different audiences.

  • Matthew Haavisto presented a survey of the new features from a user’s point of view, as well as a glimpse at what is coming for Fluid in future releases of PeopleTools.
  • Jeff Robbins covered how to take advantage of many of the new Fluid features.
  • Dave Bain covered Fluid from a mobile perspective and described how developers can build, customize, and deploy Fluid features.

Life Cycle is another important topic that was addressed in these sessions.

  • Mike Krajicek presented with a customer (Boeing) on Optimizing change delivery.
  • Dave Bain covered not only the new PeopleSoft Update Manager, but the new delivery model that will enable customers to reduce cost and receive new functionality and fixes faster.
  • Tim Durant and Vijay Gopalan presented a deep dive on all the life-cycle management tools.

There were several valuable sessions that were either new this year or perennial favorites that are updated for the latest release.

  • Pramod Agrawal demonstrated how to set up a cluster of PeopleSoft Applications including the Interaction Hub.
  • Jim Marion presented his always popular PeopleTools Tips and Techniques session.
  • Jeff Robbins covered the PeopleSoft Analytics and Reporting Suite.
  • Greg Kelly described how to secure your system against the ever changing threats that are continuously emerging.
  • Dave Bain described the continuing evolution of PeopleSoft integration technology.
  • Mark Hoernemann covered the Platforms Roadmap.
  • Jeff Robbins presented the overall tools and tech roadmap.
  • Matthew Haavisto showed how to guide users through complex or infrequently performed processes using Activity Guides.

Finally, we also offered the ever-popular panel discussions and ‘Meet the Experts’ sessions.  

If you would like to review any of our sessions, go to Oracle’s Open World site and download the presentations from the content catalog.


Database administrators may have to worry about POODLE attacks

Chris Foot - Fri, 2014-10-24 11:18

Encryption is a regular component of database security, point-of-sale implementations, network protection and a plethora of other IT considerations. 

One protocol, SSL 3.0, was recently deemed sub-par. Dark Reading noted that Google experts discovered a vulnerability in the nearly 15-year-old encoding rule that could potentially allow cybercriminals to initiate man-in-the-middle attacks against users. 

What is "man-in-the-middle"?
MITM intrusions are some of the most malicious attacks organizations can sustain. According to Computer Hope, MITM occurs when a user disrupts the path between an entity sending information and the object or person receiving the data. 

For example, if Person A delivered an email to Person C, then Person B could initiate a MITM attack, manipulate the message however he or she sees fit, and then transfer the email to Person C. As one can see, this capability is quite dangerous. 

A fault for the skilled 
Google researchers dubbed the vulnerability (CVE-20140-3566), naming the type of attack a person would launch to take advantage of this fault as Padding Oracle On Downgraded Legacy Encryption, or POODLE. Apparently, a POODLE infiltration would be incredibly difficult to pull off, meaning only the most experienced hackers are capable of using the method to their advantage. 

Next steps 
Although SSL was replaced by updated encryption protocols, it's still employed to support antiquated software and older client servers. Nevertheless, these applications and machines likely hold valuable information for many companies, and enterprises should strongly consider consulting database administration services to apply revision and new data encoding processes. 

As far as vendor-related services go, Google will remove SSL 3.0 from its client programs, while Mozilla intends to do the same on November 25. 

Despite this cause for concern, WhiteOps Chief Scientist Dan Kaminsky assured Dark Reading that it's "not as bad as Heartbleed," but still a consideration companies should take seriously. 

The post Database administrators may have to worry about POODLE attacks appeared first on Remote DBA Experts.

Recap of yesterday’s Arizona Oracle User Group (AZORA) meeting

Bobby Durrett's DBA Blog - Fri, 2014-10-24 11:15

Yesterday was the first meeting of the newly restarted Arizona Oracle User Group, AZORA.  It was a good first meeting to kick off what I hope will turn into an active club.

We met in the Tempe Public Library in a large downstairs room with a projector screen and plenty of seating with tables so it was easy to take notes.  We also had snacks.  I got a Paradise Bakery chocolate chip cookie and bottled water so that put me in a good mood for the afternoon meeting. They also had some giveaways and so I picked up an Oracle pen and notebook to use to take notes during the talk.  They also gave away three or four Rich Niemiec books and some gift cards as prizes, but, as usual, I didn’t win anything.

The first talk was about “Big Data”.  I’m skeptical about any buzzword, including big data, but I enjoyed hearing from a technical person who was actually doing this type of work.  I would rather hear an honest perspective from someone in the midst of the battle than get a rosy picture from someone who is trying to sell me something.  Interestingly, the talk was about open source big data software and not about any Oracle product, so it gave me as an Oracle database specialist some insight into something completely outside my experience.

The second talk was about another buzzword – “The Cloud”.  The second talk was as helpful as the first because the speaker had exposure to people from a variety of companies that were actually doing cloud work.  This talk was more directly related to my Oracle database work because you can have Oracle databases and applications based on Oracle databases deployed in the cloud.

It was a good first meeting and I hope to attend and help support future meetings.  Hopefully we can spread the news about the club and get even more people involved and attending so it will be even more useful to all of us.  I appreciate those who put in the effort to kick off this first meeting.

– Bobby





Categories: DBA Blogs

Minimising Parse Time in Application Engine with ReUseStatement

David Kurtz - Fri, 2014-10-24 09:15
This article explains how to determine the overhead of using literal values rather than bind variables in SQL submitted by PeopleSoft Application Engine programs. Using a combination of PeopleSoft Application Engine Batch Timings and Oracle Active Session History (ASH), it is possible to determine where it is most effective to change to alter this behaviour by setting the ReUse attribute.
ReUse Statement Flag I originally wrote about the Performance Benefits of ReUseStatement in Application Engine over 5 years ago, and the problem is no less significant today than it was then.  There are still many places in the delivered PeopleSoft application that would benefit from it.  However, it is not possible to simply set the attribute across all Application Engine programs because it will cause errors in steps where SQL is dynamically generated, so each case must be considered.  Where the ReUse attributed is changed, it must be tested and migrated like any other customisation.

Recently, I have been encountering problems in a number of places on a Financials system which were resolved by enabling ReUseStatement.  So I started by calculating how much time was lost by not setting it.
Application Engine Batch TimingsIf an Application Engine step is not reused, then it is compiled prior to every execution during which the Application Engine bind variables are resolved to literals. The number and duration of compilations and executions are reported in the Batch Timings, the production of which are controlled with the TraceAE flag in the Process Scheduler configuration file (psprcs.cfg).  

;-------------------------------------------------------------------------
; AE Tracing Bitfield
;
; Bit Type of tracing
; --- ---------------
...
; 128 - Timings Report to AET file
...
; 1024 - Timings Report to tables
...
TraceAE=1152
;------------------------------------------------------------------------

Whatever other TraceAE flags you set, I would always recommend that you set 128 and 1024 so that batch timings are always emitted to both file and database.  The overhead of enabling them is negligible because they are managed in memory at run time and physically written once when the Application Engine program ends.

NB: The settings in the configuration file can be overridden by command line options.  If you specify -TRACE parameter in the Process Scheduler definitions remember to also set these flags.

Batch timings are written to the AE Trace file at end of an Application Engine program, and to PS_BAT_TIMINGS PeopleTools tables at the successful end of an Application Engine program. 
It can be useful to have batch timings in the trace file of an Application Engine that failed because they are not written to the database.  As your system runs, you will build up batch timings for all of your successful Application Engine processes (which will be most of them.  This is a useful source of performance metrics.
Compilations, Execution and ReUseIn this example, the number of compilations is equal to the number of executions because ReUseStatement is not set.

                          PeopleSoft Application Engine Timings
(All timings in seconds)

C o m p i l e E x e c u t e F e t c h Total
SQL Statement Count Time Count Time Count Time Time
------------------------------ ------- -------- ------- -------- ------- -------- --------
99XxxXxx.Step02.S 8453 2.8 8453 685.6 0 0.0 688.4
...

With ReUse Statement enabled, there is now only a single compilation, and most of the time is saved in execution not compilation.

                               C o m p i l e    E x e c u t e    F e t c h        Total
SQL Statement Count Time Count Time Count Time Time
------------------------------ ------- -------- ------- -------- ------- -------- --------
99XxxXxx.Step02.S 1 0.0 8453 342.3 0 0.0 342.3
...

So we can use the batch timings to identify steps where ReUseStatement is not set because they have as many compilations as executions, and then we can profile the top statements.
 Profile CompilationsThis query produces a simple profile of batch timings for statements. 
  • In sub-query x it extract batch timings for statements with more than one compilation in processes that ended in the last 7 days.
  • There is a long-standing bug in batch timings where negative timings can be returned when the clock that returns milliseconds recycles back to zero every 216 milliseconds.  Sub-query y calculates an adjustment that is applied in sub-query z if the timing is negative.
  • Arbitrarily, I am only looking at statements with more than a total of 10000 compilations.

REM ReUseCand.sql
REM (c)Go-Faster COnsultancy Ltd. 2014
COLUMN detail_id FORMAT a32
COLUMN step_time FORMAT 999990 HEADING 'AE|Step|Secs'
COLUMN compile_count HEADING 'AE|Compile|Count'
COLUMN execute_count HEADING 'AE|Execute|Count'
COLUMN processes HEADING 'Num|Process|Instances'
COLUMN process_name HEADING 'Process|Name'
SPOOL ReUseCand
WITH x AS (
SELECT l.process_instance, l.process_name
, l.time_elapsed/1000 time_elapsed
, l.enddttm-l.begindttm diffdttm
, d.bat_program_name||'.'||d.detail_id detail_id
, d.compile_count, d.compile_time/1000 compile_time
, d.execute_time/1000 execute_time
FROM ps_bat_Timings_dtl d
, ps_bat_timings_log l
WHERE d.process_instance = l.process_instance
AND d.compile_count = d.execute_count
AND d.compile_count > 1
AND l.enddttm > SYSDATE-7
), y as (
SELECT x.*
, GREATEST(0,60*(60*(24*EXTRACT(day FROM diffdttm)
+EXTRACT(hour FROM diffdttm))
+EXTRACT(minute FROM diffdttm))
+EXTRACT(second FROM diffdttm)-x.time_elapsed) delta
FROM x)
, z as (
SELECT process_instance, process_name, detail_id
, CASE WHEN time_elapsed < 0 THEN time_elapsed+delta
ELSE time_elapsed END time_elapsed
, compile_count
, CASE WHEN compile_time < 0 THEN compile_time+delta
ELSE compile_time END AS compile_time
, CASE WHEN execute_time < 0 THEN execute_time+delta
ELSE execute_time END AS execute_time
FROM y
), a as (
SELECT process_name, detail_id
, SUM(compile_time+execute_time) step_time
, SUM(compile_count) compile_count
, COUNT(DISTINCT process_instance) processes
FROM z
GROUP BY process_name, detail_id)
SELECT * FROM a
WHERE compile_count >= 10000
ORDER BY step_time DESC
/
SPOOL OFF

So now I have a list of steps with lots of compilations.  I know how long they took, but I don't know how much time I might save by enabling ReUseStatement. That will save some time in Application Engine, but it will also cut down database parse time.  So now I need determine the parse time from ASH data.

WITH x AS (
Process Step Compile Process
Name DETAIL_ID SEcs Count Instances
------------ -------------------------------- ------ ---------- ----------
AP_PSTVCHR AP_PSTCOMMON.ACCT_UPD.Step02.S 12001 40704 10
AP_VCHRBLD APVEDTMOVE.UPDQTY03.Step03.S 4313 49536 28
FS_VATUPDFS FS_VATUPDFS.Seq0-b.DJ300-2.S 4057 203704 3
AP_VCHRBLD APVEDTMOVE.UPDQTY03.Step02.S 2799 49536 28
PC_BI_TO_PC PC_BI_TO_PC.UPD_PRBI.UPDATE.S 1974 23132 10
FS_VATUPDFS FS_VATUPDFS.Seq0-a.X0001.D 1960 37368 3
GL_JEDIT_0 FS_CEDT_ECFS.iTSELog.iTSELog.S 1628 13104 519
AP_APY2015 AP_APY2015.V_CREATE.Step14.H 1192 11318 19

This query is based on the previous one, but includes scalar queries on the ASH data for each step.
  • WARNING: This query can run for a long period because it has to scan ASH data for each entry in BAT_TIMINGS_DTL.
  • This time in sub-query x I am looking for a rolling 7-day period up to the last hour, because ASH data will have been flushed to the ASH repository.
  • In sub-query y there are two scalar queries that retrieve the DB time spent on hard parse, and all DB time for each batch timing entry.  These queries count 10 seconds for each distinct sample ID to estimate elapsed time rather than total DB time.
  • The query does not group by process name because one step can be called from many Application Engine programs and I want to aggregate the total time across all of them.

REM ReUseCandASH.sql
REM ReUseCandASH.sql
REM (c)Go-Faster Consultancy Ltd. 2014
COLUMN processes HEADING 'Num|Process|Instances'
COLUMN process_name HEADING 'Process|Name'
COLUMN detail_id FORMAT a32
COLUMN step_time HEADING 'AE|Step|SEcs' FORMAT 999990
COLUMN compile_count HEADING 'AE|Compile|Count'
COLUMN execute_count HEADING 'AE|Execute|Count'
COLUMN hard_parse_secs HEADING 'Hard|Parse|Secs' FORMAT 99990
COLUMN ash_secs HEADING 'DB|Time' FORMAT 99990
SPOOL ReUseCandASH
WITH x AS (
SELECT l.process_instance, l.process_name
, l.time_elapsed/1000 time_elapsed
, l.begindttm, l.enddttm
, l.enddttm-l.begindttm diffdttm
, d.bat_program_name||'.'||d.detail_id detail_id
, d.compile_count, d.compile_time/1000 compile_time
, d.execute_time/1000 execute_time
FROM ps_bat_timings_dtl d
, ps_bat_timings_log l
WHERE d.process_instance = l.process_instance
AND d.compile_count = d.execute_count
AND d.compile_count > 1
AND l.enddttm >= TRUNC(SYSDATE-7,'HH24')
AND l.enddttm < TRUNC(SYSDATE,'HH24')
), y as (
SELECT x.*
, GREATEST(0,60*(60*(24*EXTRACT(day FROM diffdttm)
+EXTRACT(hour FROM diffdttm))
+EXTRACT(minute FROM diffdttm))
+EXTRACT(second FROM diffdttm)-x.time_Elapsed) delta
FROM x)
, z as (
SELECT process_instance, process_name, detail_id
, CASE WHEN time_elapsed < 0 THEN time_elapsed+delta
ELSE time_elapsed END AS time_elapsed
, compile_count
, CASE WHEN compile_time < 0 THEN compile_time+delta
ELSE compile_time END AS compile_time
, CASE WHEN execute_time < 0 THEN execute_time+delta
ELSE execute_time END AS execute_time
, (
SELECT 10*COUNT(DISTINCT h.sample_id)
FROM psprcsque q
, dba_hist_snapshot x
, dba_hist_active_sess_history h
WHERE q.prcsinstance = y.process_instance
AND x.begin_interval_time <= y.enddttm
AND X.END_INTERVAL_TIME >= y.begindttm
AND h.sample_time between y.begindttm and y.enddttm
AND h.SNAP_id = x.SNAP_id
AND h.dbid = x.dbid
AND h.instance_number = x.instance_number
AND h.module = 'PSAE.'|| y.process_name||'.'||q.sessionidnum
AND h.action = y.detail_id
AND h.in_hard_parse = 'Y'
) hard_parse_secs
, (
SELECT 10*COUNT(DISTINCT h.sample_id)
FROM psprcsque q
, dba_hist_snapshot x
, dba_hist_active_sess_history h
WHERE q.prcsinstance = y.process_instance
AND x.begin_interval_time <= y.enddttm
AND X.END_INTERVAL_TIME >= y.begindttm
AND h.sample_time between y.begindttm and y.enddttm
AND h.SNAP_id = X.SNAP_id
AND h.dbid = x.dbid
AND h.instance_number = x.instance_number
AND h.module = 'PSAE.'|| y.process_name||'.'||q.sessionidnum
AND h.action = y.detail_id
) ash_secs
FROM y
), a AS (
SELECT /*process_name ,*/ detail_id
, SUM(compile_time+execute_time) step_time
, SUM(compile_count) compile_count
, COUNT(DISTINCT process_instance) processes
, SUM(hard_parse_secs) hard_parse_secs
, SUM(ash_secs) ash_secs
FROM z
GROUP BY /*process_name,*/ detail_id)
SELECT a.*
FROM a
WHERE compile_count >= 10000
ORDER BY step_time DESC
/
spool off

Now we can also see how much time the database is spending on hard parse on each step, and it is this time that is likely to be saved by enabling ReUseStatement.
However, before we can enable the ReUseStatement attribute we must first manually inspect each step in Application Designer and determine whether doing so would break anything.  The Comment column in this profile was added manually as I did that.  Some statements I can change, some I have to accept the overhead.

                                   Step    Compile    Process      Parse         DB
DETAIL_ID Secs Count Instances Secs Time Comment
-------------------------------- ------ ---------- ---------- ---------- ---------- …………………………………………………………………………………………………………………………………
AP_PSTCOMMON.ACCT_UPD.Step02.S 12001 40704 10 11820 11920 Set ReUseStatement
FS_CEDT_ECMB.4EditCDT.uValCDT.S 5531 10289 679 620 5870 Dynamic SQL, can't set ReUseStatement
APVEDTMOVE.UPDQTY03.Step03.S 4306 49471 27 4020 4100 Set ReUseStatement
FS_VATUPDFS.Seq0-b.DJ300-2.S 4057 203704 3 3150 3860 Dynamic SQL, can't set ReUseStatement
FS_CEDT_ECFS.iTSELog.iTSELog.S 3332 19073 716 2130 3520 Dynamic SQL, can't set ReUseStatement
APVEDTMOVE.UPDQTY03.Step02.S 2796 49471 27 2730 2820 Set ReUseStatement
PC_BI_TO_PC.UPD_PRBI.UPDATE.S 1974 23132 10 230 1920 Set ReUseStatement
FS_VATUPDFS.Seq0-a.X0001.D 1960 37368 3 0 0 Dynamic SQL, can't set ReUseStatement
FS_CEDT_ECMB.4uAnchCT.uAnchCDT.S 1319 10289 679 510 1290 Dynamic SQL, can't set ReUseStatement
AP_APY2015.V_CREATE.Step14.H 1169 11094 19 0 0 Set ReUseStatement
GL_JETSE.GA100.CHKEDT.S 1121 15776 569 860 930 Dynamic SQL, can't set ReUseStatement
FS_CEDT_ECMB.iTSELog.iTSELog.S 988 10289 679 450 990 Dynamic SQL, can't set ReUseStatement
FS_CEDT_ECMB.uMarkVal.uMarkVal.S 711 10289 679 50 670 Dynamic SQL, can't set ReUseStatement
FS_CEDT_ECMB.uMarkInv.uMarkInv.S 668 10289 679 40 790 Dynamic SQL, can't set ReUseStatement
  • Due to a bug in the instrumentation of Application Engine, the session's action attribute is not set for Do Select (type D) and Do When (type H) steps.  ASH data cannot therefore be matched for them.
  • More DB Time is reported for FS_CEDT_ECMB.uMarkInv.uMarkInv.S than is reported by batch timings.  This is a consequence of ASH sampling, where we count 10 seconds for each sample.
ConclusionSetting ReUseStatement is very simple because it doesn't involve changing SQL, but there are lots of places where it could be set.  This technique picks out the relatively few places where doing so could potentially have a significant effect.
    ©David Kurtz, Go-Faster Consultancy Ltd.

    Glorious Data Visualizations for Your Friday

    Oracle AppsLab - Fri, 2014-10-24 09:00

    If you’ve read here for more than a hot minute, you’ll know that I love me some data visualization.

    This love affair dates back to when Paul (@ppedrazzi) pointed me to Hans Rosling’s (@hansrosling) first TED talk. I’m sure Hans has inspired an enormous city of people by now, judging by the 8 million plus views his TED talk has garnered. Sure, those aren’t unique view, but even so.

    There’s an interesting meta-project: visualize the people influenced by various visualization experts, like a coaching tree or something.

    sandwich

    Classic comic from xkcd, used under CC 2.5

    Back on track, if you haven’t yet, watch the BBC documentary on him, “The Joy of Stats,” fantastic stuff, or if you have seen it, watch it again.

    As luck would have it, one area of specialization of our newest team members is, wait for it, data visualization.

    Last week, I got to see them in action in a full-day workshop on data visualization, which was eye-opening and very informative.

    I’m hoping to get a few blog posts out of them on the subject, and while we wait, I wanted to share some interesting examples we’ve been throwing around in email.

    I started the conversation with xkcd because, of course I did. Randal Munroe’s epic comic isn’t usually mentioned as a source for data visualizations, but if you read it, you’ll know that he has a knack for exactly that. Checking out the Google Image search for “xkcd data visualization” reminded me of just how many graphs, charts, maps, etc. Randal has produced over the years.

    I also discovered that someone has created a D3 chart library as an homage to the xkcd style.

    Anyway, two of my favorite xkcd visualizations are recent, possibly a function of my failing memory and not coincidence, Pixels and Click and Drag.

    I probably spent 10 minutes zooming into Pixels, trying to find the bottom; being small-minded, I gave up pretty early on Click and Drag, assuming it was small. It’s not.

    How much time did you spend, cough, waste, on these?

    During our conversation, a couple interesting examples have come back to me, both worth sharing.

    First is Art of the Title, dedicated to the opening credits of various films. In a very specific way, opening credits are data visualizations; they set the mood for the film and name the people responsible for it.

    Second is Scale of the Universe, which is self-explanatory and addictive.

    So, there you go. Enjoy investigating those two and watch this space for more visualization content.

    And find the comments.Possibly Related Posts:

    APEX Tabular Forms Deep Dive at #ORCLAPEX NYC Meetup

    Marc Sewtz - Fri, 2014-10-24 08:35
    Ever heard of APEX$ROW_NUM, APEX$ROW_SELECTOR and APEX$ROW_STATUS? Did you know you can reference tabular form columns using bind variable syntax in your page processes? Are you familiar with the execution scope of tabular form proecesses for modified vs submitted rows? Ever struggled using apex_application.g_fxx arrays with checkboxes? And do you really need to use the apex_item APIs? In APEX 4.0, 4.1 and 4.2 we've introduced lots of new tabular form features, including declarative tabular form validations and page processes, yet there are still PL/SQL processes beeing written that loop through the apex_application.g_fxx arrays or tabular form regions that mix apex_item API calls with using built-in form elements.

    So when planning our next #ORCLAPEX NYC Meetup, we've figured this would be a great topic to cover. And since we don't want to talk you to death during a single meetup - we decided that we'll do a three part series, starting slow and covering the basics during our next meetup on 11/06, then dig a little deeper in part two and then conclude this series with a grand finale during the third meetup in this series.

    Join us on Thurday, November 6th at the Oracle office in Manhattan, at 120 Park Ave. We're starting at 6pm and we'll serve pizza this time - thanks to everyone's generous donations last time.

    RSVP today:  #ORCLAPEX NYC Meetup

    OCP 12C – Resource Manager and Performance Enhancements

    DBA Scripts and Articles - Fri, 2014-10-24 07:16

    Use Resource Manager for a CDB and a PDB Managing Resources between PDBs The Resource Manager uses Shares ans Utilization limit to manage resources allocated to PDBs. The more “Shares” you allocate to a PDB, the more resource it will have. Shares are allocated through DBMS_RESOURCE_MANAGER.CREATE_CDB_PLAN_DIRECTIVE. One directive can only concern one PDB and you can’t [...]

    The post OCP 12C – Resource Manager and Performance Enhancements appeared first on Oracle DBA Scripts and Articles (Montreal).

    Categories: DBA Blogs

    Download Oracle VirtualBox VM with Database version 12.1.0.2

    Marco Gralike - Fri, 2014-10-24 06:35
    Somehow missed the announcement that the Oracle Development team refreshed (9/26/2014) the VirtualBox Oracle Database…

    Documentum Multiple ADTS: Ratio of rendition creations between instances (Part 1)

    Yann Neuhaus - Fri, 2014-10-24 04:34

    I recently realized there are very few information about multiple ADTS instances installation and management. And we have a customer which has two ADTS running for one docbase. I therefore decided to write a couple of blog posts on "Multiple ADTS", since it is likely to be helpful for someone in the same situation.

     

    Rendition processing & ADTS instance

    This first blog post will tell you how to find out which rendition has been processed by which ADTS instance. Hence, we will be able to calculate what the ratio of processing between two (or more) servers is. The blog post will be split in two parts as I know two ways of getting the same result.

    The best architecture is to have 2 dedicated servers for the two different ADTS instances. Let assume we have SERVER1 and SERVER2. Each one is hosting an instance of ADTS with the same version and patch level. We will see in another blog how to configure both servers to allow a kind of load balancing.

    You have to find some audit or log in the docbase to figure out when a rendition was requested. When a rendition is requested, it is posted in the dmi_queue_items queue, but you may already be aware of this. If not, you can take a short look at my other blog post (http://www.dbi-services.com/index.php/blog/entry/documentum-useful-queries-dqliapi) which presents some DQLS to find what is pending in the rendition queue.

    But unfortunately, when the rendition is done, the queued item is deleted and all your hope of getting some information vanishes with it. We can get a little hint if the rendition failed as it is kept in this queue, but with the attribute event set to DTS (go to my other post for more info). But still, it doesn't show which server failed it.

     

    CTS Report

    In this part I will talk about the CTS report method. In the next part of this blog post, I will introduce the dmr_content method.

    If you heard about Digital Asset Manager you may be on the way. It is an EMC tool which can be used to generate reports of ADTS activities. If you can get it, just use it, as it is exactly what you need. But in this post, I will show you how to do without. So, just like Digital Asset Manager, you will have to enable a specific option in the DA which is not enabled by default. It is some kind of audit of ADTS activities and it will populate a table with rendition information and, as you may guess, with information of which server processed each rendition.

    Login to your DA as installation owner and navigate to Content Transformation Services and then to CTS Reporting Configuration. You should see the following form:

    MA-RR-01_config_report.PNG

    Now check Reporting Configuration to "On", select Archiving Configuration mode and put 7 for Archiving Interval. You just configured your repository to write an entry each time a rendition is done by whichever server with a seven days period before archiving. It means that each time a rendition is done, an entry will be stored in dm_cts_response table and after seven days (starting now), all entries will be stored in dmc_cts_request_archiving. With DQL, you can look at these tables which should be empty because no rendition was done yet.

     

    select * from dm_cts_response;
    select * from dmc_cts_request_archiving;

     

    After a period of time, let's say 5 days, some entries have been added to the first table. Only if you have rendition activities of course. And you can check which server is involved in the process thanks to the following query:

     

    select cts_server_name, trans_completed_time from dm_cts_response order by trans_completed_time desc;

    MA-RR-01_config_report2.PNG

    Conclusion

    These tables are used by the EMC tool Digital Asset Manager and if you know a little DQL, you can build some stats without buying the tool. However there is a drawback for this method: In fact, you enable some kind of audit trace which will populate 2 tables in the repository. Hence, if you have a lot of rendering processes, the tables could grow fast and it will need space. In addition, you will not be able to trace older renditions as everything is stored only since you activated the report.

    But it's quite useful as you can get the rendition ratio in the following way:

     

    select cts_server_name, count(*) as val from dm_cts_response group by cts_server_name;

    RDX services: Platform-specific Expertise [VIDEO]

    Chris Foot - Fri, 2014-10-24 04:28

    Transcript

    Hi, welcome to RDX. RDX has a wide range of platform-specific experience to help keep your database environment highly available and high performance. Our DBAs can help supplement any gaps in skill sets, leaving your internal team to focus on the tasks they do best.

    Whether you prefer to store information in SQL Server, Oracle, MySQL, PostgreSQL or Hyperion/Essbase, our specialists provide you with a wealth of expertise and support. Our staff is well-versed in optimizing and protecting all of these environments 24×7, providing your business with a greater peace of mind.

    In addition to our varied expertise, we provide clients with the choice of customizing their environments. We’re here to accommodate any of your unique business needs, and our DBA experts are equipped to solve your toughest challenges.

    Thanks for watching. Be sure to watch next time.
     

    The post RDX services: Platform-specific Expertise [VIDEO] appeared first on Remote DBA Experts.

    From 8.0 extended rowid to 12c pluggable db, or why Oracle Database is still a great software

    Yann Neuhaus - Fri, 2014-10-24 03:31

    Do you know why Oracle Database is leading the database market for more than 30 years? Do you know any other software that is still the main solution after decades? It think that it is because Oracle is a good software from the get go. Many early decisions in the software architecture have revealed later how they were the right decision. Several decisions, such as the C language that made it portable to all platforms that were relevant during those 30 years, or the parallel server that has brought RAC when standalone servers were not able to scale anymore. Here I'll illustrate a decision made 15 years ago that has made the whole 12c pluggable databases stuff possible.

    Oracle 7 rowid

    The ROWID is the physical address of a row in the database. Physically, a database is just a set of files where data is stored as rows in blocks. So, at the time of Oracle 7, the ROWID was just:

    • the file number
    • the block number in that file
    • the row number in that block
    That was printed in hexadecimal when we selected the ROWID pseudo-column:

    CaptureOracle7Rowid.PNG

    Here my rows are numeroted from 0 to 3 and are all in the file 2 at block offset 58 (0x3a)

    At that time, the maximum number of datafiles in a database was 1022. That was enough until the datawarehouse days brought the need for Very Large DataBases.

    relative file number

    In Oracle 8 they wanted to increase the possible number of datafile without changing the rowid (which would have involved updating all blocks during an upgrade). And here is how they did:

    1. they introduced the 'relative file number'. The file identification part is not unique for the database but only for the tablespace. That means that you can have 1022 datafiles per tablespace instead of 1022 datafiles per database.

    2. they kept the relative_fno equal to the absolute file_id for the first 1022 datafiles, so that an Oracle 7 datafile is still compatible: the number that was the absolute file_id being now the relative_fno.

    3. when going over the 1022 datafiles per database, the file_id can go beyond (it is not stored in the rowid) but the relative_fno just recycles to 1.

    The consequence is that a rowid is not unique anymore. When we have to look for a row by its rowid, we must know on which tablespace it is, because the rowid identification is related to the tablespace.

    No problem. Where are the rowid used? In chained rows, to get the other piece. A table cannot store parts of its row in different tablespace, so we know that if we are in tablespace USERS and have a pointer to a chained row, it has to be in the USER tablespace as well. They are used in indexes as well. And that's the same: if we unique scan the index EMP_PK to get the rowid of the EMP row, we know that it is in the USERS tablespace because we know that EMP table is in the USERS tablespace.

    However, a new feature appeared at that time: a table can be partitioned. We cannot say anymore that EMP rows are in USERS tablespace because EMP may be partitioned over several tablespaces. That does not concern chained rows. That does not concern non-partitioned tables. And with partitioned tables, that does not concern local indexes because local index partitions always address rows in one table partition.

    This is where Oracle has introduced an extended rowid, for global indexes on partitioned tables, which is larger and is able to give the tablespace information in addition to the relative file number. It is called extended as opposite to 'restricted' rowid which is restricted to cases where we know the tablespace.

    They could have choosen to store only the tablespace number. But they choose to store the object id instead, which - from the dictionary - can be used to know the tablespace. People were puzzled by that choice at that time, but it has been revealed later as a great software architecture decision because this is what allows us to have pluggable databases 15 years later.

    But before that, I have to introduce another 8.0 change, which is the data object id.

    data object id

    In Oracle 7, there is only one object_id. Because there is a one-to-one relation between the logical object (table) and the physical object (segment). That has changed with the introduction of partitioning, where one table may have several partitions.

    Partitions are also logical objects, and each of them has a unique object_id. But once again, the software architects made a very good decision by separating the identification of the physical object: they introduced the data_object_id. When you create a table, the data_object_id of the segment is equal to the object_id of the table. But that can change.

    For example, what happens when you truncate a table? You just reset the high water mark without going to all the blocks. And you can insert new rows that may go into the same place. But how are you sure that concurrent users will not mix old blocks with new ones ? Remember that the reader do not lock anything, so they didn't notice your truncate. The truncate changes the data object_id as if it were a new segment, but still related to the same logical table.

    And with partitioning you can exchange partition: the logical object_id changes but the physical data_object_id is still the same because it is still the same segment.

    It's not always easy to know if we are dealing with an object_id or a data_object_id. Basically, things that are logical (for example lock table or lock partition) show the object_id and they are - depending on the place - called OBJECT_ID, OBJ# or OBJN. When it is related to the physical segment (blocks in buffer cache, block corruption) we see DATA_OBJECT_ID, DATAOBJ# or OBJD or even OBJ or OBJECT_NUMBER.

    When I'm not sure about what to use in my scripts, I test it on a table that has been truncated at least once, so that both values are different. I advise you to always test your scripts on a table that has been truncated and on a tablespace that has been transported.

    Oracle 8 extended rowid

    So I said that the extended rowid does not store the tablespace id. It stores the data_object_id, which is associated with one and only one tablespace. The format of the extended rowid is:

    • the data object id (which gives the tablespace from the data dictionary)
    • the relative file number (relative to the tablespace)
    • the block number in that file
    • the row number in that block

    and it is needed only when we don't navigate from an object which can be used to define exactly which tablespace can contain the segment.

    Now let's understand why the Oracle software architects have decided to store the data_object_id instead of the tablespace number. By doing that, they made the tablespaces physically independant of the database.

    pluggable tablespaces

    Yes, I know it's called transportable tablespaces. But it was the premise of pluggable database. Anyway, pluggable databases are just transportable tablespaces that include the system tablespace (which contain the metadata for the other tablespaces).

    You can transport a tablespace from another database and plug it in you database. Of course, the absolute file number will change as it is assigned by your database. The tablespace number will change. But the relative file numbers - relative to the tablespace - do not have to change. 

    And of course the object_id will change: a new one will be used when importing the metadata. But the data_object_id do not have to change. The reason is that data_object_id is not expected to be unique in the database. It must be unique only whithin the object (two partitions of the same table cannot have the same data_object_id).

    Oracle 8 extended rowid was designed for pluggable databases

    And this is where those early decisions have all their meaning. You can plug a tablespace and the rowid of the rows in that tablespace do not have to be updated. This is what makes it a quick operation because only the dictionary and the file headers have to be updated. The time depends on the number of objects, but not on the volume of data. The agility brought by pluggable databases in 2013 were actually designed in 1997.

    rowid is not unique

    So I said that rowid is not unique? Let's prove it. I create a table DEMO2 in tablespace DEMO1, export that tablespace, rename the table to DEMO2 to DEMO1, import that tablespace as DEMO2, so that I have now two tables DEMO1 and DEMO2 respectively in tablespaces DEMO1 and DEMO1.

    Here is my table:

    SQL> select dname,rowid
      2   ,dbms_rowid.rowid_object(rowid) data_object_id
      3   ,dbms_rowid.rowid_relative_fno(rowid) relative_fno
      4   ,dbms_rowid.rowid_block_number(rowid) block_id
      5   ,dbms_rowid.rowid_row_number(rowid) row_number
      6   from DEMO2
      7  /
    
    DNAME          ROWID              DATA_OBJECT_ID RELATIVE_FNO   BLOCK_ID ROW_NUMBER
    -------------- ------------------ -------------- ------------ ---------- ----------
    ACCOUNTING     AAAX7uAACAAAACEAAA          98030            2        132          0
    RESEARCH       AAAX7uAACAAAACEAAB          98030            2        132          1
    SALES          AAAX7uAACAAAACEAAC          98030            2        132          2
    OPERATIONS     AAAX7uAACAAAACEAAD          98030            2        132          3
    

    I export the tablespace with transportable tablespaces:

    SQL> host expdp '"/ as sysdba"' transport_tablespaces=DEMO1
    Export: Release 12.1.0.2.0 - Production on Fri Oct 24 15:33:35 2014
    Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.
    Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
    With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
    Starting "SYS"."SYS_EXPORT_TRANSPORTABLE_01":  "/******** AS SYSDBA" transport_tablespaces=DEMO1
    Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
    Processing object type TRANSPORTABLE_EXPORT/TABLE
    Processing object type TRANSPORTABLE_EXPORT/TABLE_STATISTICS
    Processing object type TRANSPORTABLE_EXPORT/STATISTICS/MARKER
    Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
    Master table "SYS"."SYS_EXPORT_TRANSPORTABLE_01" successfully loaded/unloaded
    ******************************************************************************
    Dump file set for SYS.SYS_EXPORT_TRANSPORTABLE_01 is:
      /u02/app/oracle/admin/DEMO/dpdump/expdat.dmp
    ******************************************************************************
    Datafiles required for transportable tablespace DEMO1:
      /tmp/demo1.dbf
    Job "SYS"."SYS_EXPORT_TRANSPORTABLE_01" successfully completed at Fri Oct 24 15:34:35 2014 elapsed 0 00:00:59
    SQL> host cp '/tmp/demo1.dbf' '/tmp/demo2.dbf'
    SQL> alter tablespace DEMO1 read write;
    Tablespace altered.

    Then import it to DEMO2 tablespace (after renaming my previous table)

    SQL> rename DEMO2 to DEMO1;
    SQL> host impdp '"/ as sysdba"' transport_datafiles='/tmp/demo2.dbf ' remap_tablespace='DEMO1:DEMO2';
    Import: Release 12.1.0.2.0 - Production on Fri Oct 24 15:34:35 2014
    
    Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.
    
    Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
    With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
    Master table "SYS"."SYS_IMPORT_TRANSPORTABLE_01" successfully loaded/unloaded
    Starting "SYS"."SYS_IMPORT_TRANSPORTABLE_01":  "/******** AS SYSDBA" transport_datafiles=/tmp/demo2.dbf  remap_tablespace=DEMO1:DEMO2
    Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
    Processing object type TRANSPORTABLE_EXPORT/TABLE
    Processing object type TRANSPORTABLE_EXPORT/TABLE_STATISTICS
    Processing object type TRANSPORTABLE_EXPORT/STATISTICS/MARKER
    Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
    Job "SYS"."SYS_IMPORT_TRANSPORTABLE_01" successfully completed at Fri Oct 24 15:34:49 2014 elapsed 0 00:00:13
    SQL> alter tablespace DEMO2 read write;
    Tablespace altered.
    

    Now I have 2 tables with different object_id but same data_object_id:

    SQL> select object_name,object_id,data_object_id from user_objects where object_name like 'DEMO_';
    
    OBJECT_NAM  OBJECT_ID DATA_OBJECT_ID
    ---------- ---------- --------------
    DEMO2           98076          98030
    DEMO1           98029          98030
    

    And 2 segments in different files (file_id) but same relative_fno:

    SQL> select segment_name,tablespace_name,file_id,relative_fno,block_id from dba_extents where segment_name like 
    
    SEGMENT_NAME    TABLESPACE    FILE_ID RELATIVE_FNO   BLOCK_ID
    --------------- ---------- ---------- ------------ ----------
    DEMO1           DEMO1               2            2        128
    DEMO2           DEMO2               4            2        128
    

    I update the rows so that I be sure to select on different tables

    SQL> update DEMO1 set dname=upper(dname);
    4 rows updated.
    SQL> update DEMO2 set dname=lower(dname);
    4 rows updated.
    

    And now showing the ROWID from both tables:

    SQL> select dname,rowid
      2   ,dbms_rowid.rowid_object(rowid) data_object_id
      3   ,dbms_rowid.rowid_relative_fno(rowid) relative_fno
      4   ,dbms_rowid.rowid_block_number(rowid) block_id
      5   ,dbms_rowid.rowid_row_number(rowid) row_number
      6   from DEMO1
      7  /
    
    DNAME          ROWID              DATA_OBJECT_ID RELATIVE_FNO   BLOCK_ID ROW_NUMBER
    -------------- ------------------ -------------- ------------ ---------- ----------
    ACCOUNTING     AAAX7uAACAAAACEAAA          98030            2        132          0
    RESEARCH       AAAX7uAACAAAACEAAB          98030            2        132          1
    SALES          AAAX7uAACAAAACEAAC          98030            2        132          2
    OPERATIONS     AAAX7uAACAAAACEAAD          98030            2        132          3
    
    SQL> select dname,rowid
      2   ,dbms_rowid.rowid_object(rowid) data_object_id
      3   ,dbms_rowid.rowid_relative_fno(rowid) relative_fno
      4   ,dbms_rowid.rowid_block_number(rowid) block_id
      5   ,dbms_rowid.rowid_row_number(rowid) row_number
      6   from DEMO2
      7  /
    
    DNAME          ROWID              DATA_OBJECT_ID RELATIVE_FNO   BLOCK_ID ROW_NUMBER
    -------------- ------------------ -------------- ------------ ---------- ----------
    accounting     AAAX7uAACAAAACEAAA          98030            2        132          0
    research       AAAX7uAACAAAACEAAB          98030            2        132          1
    sales          AAAX7uAACAAAACEAAC          98030            2        132          2
    operations     AAAX7uAACAAAACEAAD          98030            2        132          3
    

    Conclusion: I have in my database two different tables with sames rowid because it is a physical copy. Only the data dictionary makes the difference.

    PeopleTools 8.54 Feature: ExcelToCI Errors and Warnings Worksheet

    Javier Delgado - Fri, 2014-10-24 02:58
    Some years ago, I wrote this post on ExcelToCI limitations. One of the limitations I've found annoying in the past was the need to move the mouse over each Warning or Error result cell. It was not just annoying, it actually didn't allow the users to easily work on the different error types and analyze useful information such as the most common error messages, how many rows would go through if they solved a particular issue, etc.



    PeopleTools 8.54 has introduced a new worksheet showing all the warning and error messages. The following screenshot provides a clear view on how the information is presented:



    From that point on, the users may analyze the messages using Excel dynamic tables, filters, etc. Yet, there is some room for improvement. The most obvious one is to put each particular error in a different Excel row. That would make error analysis much richer.

    Let's see how this evolves with the next releases of PeopleTools.

    Upgrading PeopleTools with Zero Downtime (3/3)

    Javier Delgado - Fri, 2014-10-24 02:57
    In the past post, we covered the approach we followed to have both PeopleTools versions running at once. In this post we will cover the issues we faced and how we got around.

    Missing Tables

    Our approach for identifying tables was not perfect. By examining the traces and logs, we missed some tables that were directly impacted by Application Designer (we did not enable tracing while copying the PeopleTools projects as it would have taken too much time to complete, so we did not have those tables in our lists). Fortunately enough, we only had to adjust the list of tables a couple of times and we were up and running.

    Sequences

    For performance reasons, the customer had created Oracle Sequences instead of using the PeopleTools auto-numbering approach. In order to have the sequences in sync between both databases we followed the same approach we applied to the tables, deleting one of the sequences and creating a synonym pointing to the sequence in the other instance.

    Performance

    Most of the performance in running SQL statements is impacted by how the database builds the execution plans. The execution plans are generated taking into account the database statistics. When using DB Links, the database on which the SQL sentence is executed does not have statistics information of the linked tables, so the execution plan may not be optimal. This is particularly true for mixed queries involving local and linked tables.

    During our testing, we identified a few performance issues which required to make the table physically present in both environments. This was not always possible, but for tables that are quite unlikely to change (for instance Business Unit options), it was definitely an option.

    Data Types

    Some of the data types used by PeopleTools changed from one release to the other. In our case, both timestamps and memo fields (LONG to CLOBS) had changed types. If the table is linked, only one of the data types could be used. In this sense, we were fortunate, as the data types used by the target PeopleTools release worked correctly in the previous release, so we could use the new ones.

    Commit

    In earlier releases of the Oracle Database, each insert/update/delete operation done against a DB Link table would commit immediately (and implicitly). This would pose a serious integrity issue. Luckily, both the source and the target database releases supported global transactions (which means that the data is committed when the system explicitly calls the commit command).

    Conclusions

    In the end, our attempt was successful. However, as you can see from the issues we faced, it cannot be concluded that it will work for all types of PeopleTools upgrades nor for certain customer requirements. Still, I thought it was a creative approach and that's why I decided to share it in this blog.

    Upgrading PeopleTools with Zero Downtime (2/3)

    Javier Delgado - Fri, 2014-10-24 02:57
    Continuing with my previous blog entry, the requirement from our customer was to be able to move users back and forth between the old and new PeopleTools releases until the latter was stabilised.

    This naturally required both PeopleTools versions to coexist. Now, as you know, you cannot just install the new PeopleTools release binaries and point them to the new release. Each PeopleTools release can only connect to a database for which the PSSTATUS.TOOLSREL field contains the corresponding version value. But this is not the only problem, also part of the data model and values on the tables changes from one PeopleTools version to the other.

    Therefore, we needed a database for each PeopleTools release, with its full stack of Application Server, Web Server, Process Scheduler, etc. The idea was to give users either the new or the old URL to access the environment, being able to rapidly switch from one instance to the other. Now, in order to maintain the data in sync between both instances, we needed to implement some kind of data replication between them, which should only cover the tables not impacted by the PeopleTools upgrade process.

    There are a couple of ways in which the PeopleTools tables could be identified. For instance, the PPLTLS84CUR project may probably contain all of them. Another source could be the mvprdexp.dms script. Instead of using those methods, we decided to search for the impacted tables using a regular expression search tool by looking at the logs and traces of the PeopleTools upgrade done again a copy of the Demo environment. Although it required more work, and a few test iterations until we got it right, it allowed us to keep the number of non-replicated tables to a minimum.

    When we finally got a list of tables, we let the key user know which functionalities would not be shared by both environments. As it turned out, Process Monitor, Query or Report Manager would need to be used separately. Fortunately enough, those functionalities did not pose a big issue from an user perspective, so we could move forward.

    The next step was to decide which replication method we would use. Both databases were Oracle, although on different versions (no version was supported by both PeopleTools releases) (*). For many of the tables, we needed a bidirectional replication, as users were expected to enter transactions in any of the two environments.

    There are many products and solutions that provide data replication with Oracle databases. We finally opted for a very simple one, which is not strictly replication: Oracle DB Link. We kept the application tables in the old PeopleTools instance, and then replaced the same tables in the new PeopleTools instance by synonyms pointing to the other instance using the DB Link. Once the new PeopleTools release was stabilised, we would move the physical tables to the target instance and create the DB Link on the other side.

    Once we implemented this approach, we started testing. During testing, we faced some challenges, but we will cover them in the next and final post.

    (*) This was unlucky. If we were using the same database version, we could have used a different schema for each PeopleTools release, and instead of creating a DB Link, we could have just used synonyms and avoid some of the issues brought by DB Links.

    Upgrading PeopleTools with Zero Downtime (1/3)

    Javier Delgado - Fri, 2014-10-24 02:57
    A few months ago, BNB concluded a PeopleTools upgrade with a quite curious approach. Our customer, a leading Spanish financial institution, had PeopleSoft CRM 8.4 installation running under PeopleTools 8.42. Their CRM application was being used to provide support to their 24x7 call centres, and the only reason they had to perform the PeopleTools upgrade was to be able to update their database and WebLogic releases, as the existing ones were already out of support.

    Now, the organisation was going under a major structural change, so the customer wanted to perform the PeopleTools upgrade with a minimal disruption of their activities, as it was difficult at that time to obtain the needed sponsorship from higher managerial levels. In other words, they wanted to perform the upgrade as silently as possible. This translated in two particular requirements:
    • Ability to perform the PeopleTools change with zero downtime, in order to avoid any impact on the users.
    • Ability to gradually move users from the old PeopleTools release to the new one, practically limiting the impact of any product issue related to the upgrade. In case anything failed, they wanted to be able to move the users back to the old release.
    Having performed quite a few PeopleTools upgrades in the past, I knew that following the standard procedures would not help us in providing a satisfactory answer to the client. So, after some discussions, the customer agreed on trying a non-standard way of upgrade PeopleTools. We agreed to do a prototype, test and if everything went well, then move to Production. If it did not work out, we would need to do it in the standard way. As it finally turned out, the suggested approach worked out.

    I cannot say it would work for any other combination of PeopleTools and application versions, nor different customer usage of the application. Anyhow, I thought it may be useful to share it with you, in case any of you can enrich the approach with your feedback. In the next post I will describe the approach and in the third and final one I will describe the issues we faced during the implementation. So... keep tuned ;).

    The new %SelectDummyTable MetaSQL

    Javier Delgado - Fri, 2014-10-24 02:57
    Does anyone know a PeopleSoft developer who didn't ever use a SQL statement like the following one?

    select %CurrentDateOut
    from PS_INSTALLATION;

    Where PS_INSTALLATION could be any single-row table in the PeopleSoft data model.

    If you look at the previous statement, the SELECT clause is not retrieving any field from the PS_INSTALLATION table, but just using it to comply with ANSI SQL. The same statement could be written in Microsoft SQL Server like this:

    select %CurrentDateOut;

    In Oracle Database, as:

    select %CurrentDateOut
    from dual;

    In both cases, the sentences are a better performing option. Both solutions do not require accessing any physical table.

    The problem with these solutions is that they are platform specific, and we want to avoid platform specific syntax. Believe me, when you perform a platform migration you suddenly have very present in your mind the ancestors of the programmers who used this type of syntax. So, up to now, we had to stick with the SELECT ... FROM PS_INSTALLATION solution.








    Until now. PeopleTools 8.54 introduces a new MetaSQL name %SelectDummyTable, which automatically translates into a platform specific sentences. Our previous sample would be written as:

    select %CurrentDateOut
    from %SelectDummyTable

    We now have a platform independent and well performing solution. What else can we ask for? ;-)

    Note: I've checked the online PeopleBooks from Oracle and at this point there is no documentation on this Meta SQL. Still, I've conducted some tests and it seems to be working correctly.

    Using Global Temporary Tables in Application Engine

    Javier Delgado - Fri, 2014-10-24 02:57
    One of the new PeopleTools 8.54 features that went probably a bit unnoticed amidst the excitement on the new Fluid interface is the ability of Application Engine programs to take advantage of Global Temporary Tables (GTTs) when using an Oracle Database.

    What are GTTs?
    The Global Temporary Tables were introduced by Oracle already on the 8i version of its database product. These tables are session specific, meaning that the data inserted in them only lasts until the session is closed (in Oracle Database there is the possibility of using them only until the next commit, but that option is not used by PeopleSoft). The data inserted in the table by each session is not seen by other sessions. In other words, it is a very similar behavior to Application Engine Temporary Tables. The benefit of using a database supported solution rather the traditional temporary tables is better performance, since GTTs are optimized for temporary data.
    How is it implemented in PeopleTools?

    The implementation in PeopleTools is quite simple. When selecting Temporary Table as the record type, a new option is enabled: "Global Temporary Table (GTT)".













    The build SQL generated by PeopleTools is slightly different to traditional tables:
    CREATE GLOBAL TEMPORARY TABLE PS_BN_JOB_WRK (PROCESS_INSTANCE DECIMAL(10) NOT NULL,   EMPLID VARCHAR2(11) NOT NULL,   EMPL_RCD SMALLINT NOT NULL,   EFFDT DATE,   EFFSEQ SMALLINT NOT NULL,   EMPL_STATUS VARCHAR2(1) NOT NULL) ON COMMIT PRESERVE ROWS TABLESPACE BNAPP/
    Note: The SQL Build process still creates as many instances of the table as it did with traditional temporary tables. This sounds like a bug to me, as my guess is that the whole idea of using GTTs is to be able to share a table without actually sharing the data, but I may be wrong. In any case, it does not do any harm. Any insight on this? 

    Constraints
    Due to specific characteristics of GTTs, there are some limitations regarding when they can be used:
    • If the Application Engine is run in online mode, then the GTTs cannot be shared between different programs on the same run.
    • You cannot use Restart Enabled with GTTs as the data is deleted when the session ends. In its current version, PeopleBooks state otherwise, but I think it is a typo.
    • %UpdateStats are not supported. Before Oracle Database 12c, if the statistics would be shared among all the sessions. Oracle Database 12c also supports session specific statistics, which would be the desired behavior in PeopleSoft (from a higher level point of view, programmers are expecting the temporary table to be dedicated to the instance). I guess the %UpdateStats is not supported because Oracle Database 11g is still supported by PeopleTools 8.54 and in that case running statistics would generate unexpected results. Still, the DBA can run statistics outside of the Application Engine program.
    Note: As learnt from Oracle OpenWorld 2014, Oracle is evaluating supporting of Oracle Database 12c session specific statistics for GTT’s in a future releases of PeopleTools.
    Conclusion
    If you are moving to PeopleTools 8.54 and you want to improve the performance of a given Application Engine program, the GTTs may bring good value to your implementation. Please remember that you need to be using an Oracle Database.

      New Integration Network Utilities in PeopleTools 8.54

      Javier Delgado - Fri, 2014-10-24 02:57
      The new integration features available in PeopleTools 8.54 include better support for REST services and the new Integration Network WorkCenter. There are plenty of things to test and eventually use that may be of interest of anyone upgrading to this PeopleTools release. However, today I will focus on two simple but quite handy utilities:

      Saving Gateway Metadata

      There is a new functionality that saves the integrationgateway.properties configuration file in the database for future use or deployment on other gateway instances.



      It has happened to me a couple of times that I did redeploy of PIA that reset the configuration file to the default version. Ok, it wasn't very clever of me, as I could easily take a backup of the file before doing the redeploy, but this save to database button seems easier to use than navigating through the endless PIA directory structure.

      Node Lockdown

      Another handy feature that allows us to block certain attributes of Nodes, so they are not overwritten when performing an Application Designer project copy.

      The page used to lock the attributes is the following:



      You just need to pick which attributes should be locked and for which nodes.

      Both seem nice and useful utilities delivered by PeopleTools 8.54. I hope you also find them of interest.