Feed aggregator

One of OBIEE’s many great strengths is aggregate navigation; the ability to choose from a list of possible tables the one which will probably give the optimal performance for a given user query. Users are blissfully unaware of which particular table their query is being satisfied from, since aggregate navigation happens on the BI Server once the user’s request comes through from an Analysis or Dashboard.

This seamless nature of aggregate navigation means that testing specific aggregates are working can be fiddly. We want to ensure that the aggregates we’ve built are (i) being used when appropriate and (ii) showing the correct data. This is the particularly the case in Exalytics when aggregates are put into in-memory (TimesTen) by the Summary Advisor and we need to validate them.

Whilst the log file nqquery.log (or Usage Tracking table S_NQ_DB_ACCT) tells us pretty easily which table a query used, it is nice to be able to switch a query easily between possible aggregate sources to be able to compare the data. This blog demonstrates how we can use the INACTIVE_SCHEMAS variable (as described in my previous blog on loading Exalytics incrementally) to do this.

INACTIVE_SCHEMAS is a Logical SQL variable that tells the BI Server to exclude the specified physical schema(s) from consideration for resolving an inbound query. Normally, the BI Server will parse each incoming query through the RPD, and where a Logical Table has multiple Logical Table Sources it will evaluate each one to determine if it (a) can satisfy the query and (b) whether it will be the most efficient one to use. By using INACTIVE_SCHEMAS we can force the BI Server to ignore certain Logical Table Sources (those associated with the physical schema specified), ensuring that it just queries the source(s) we want it to.

In the following example, the data exists on both Oracle database, and TimesTen (in-memory). Whilst the example here is based on an Exalytics architecture, the principle should be exactly the same regardless of where the aggregates reside. This is how the RPD is set up for the Fact table in my example:

The GCBC_SALES schema on Oracle holds the unaggregated sales data, whilst the EXALYTICS schema on TimesTen has an aggregate of this data in it. The very simple report pictured here shows sales by month, and additionally uses a Logical SQL view to show the contents of the query being sent to the BI Server:

Looking at nqquery.log we can see the query by default hits the TimesTen source:

[...]
------------- Sending query to database named TimesTen aggregates
WITH
SAWITH0 AS (select distinct T1528.Sale_Amoun000000AD as c1,
     T1514.Month_YYYY000000D0 as c2
from
     SA_Month0000011E T1514,
     ag_sales_month T1528
[...]

Now, for thoroughness, let’s compare this to what’s in the TimesTen database, using a Direct Database Request:

OK, all looks good. But, is what we’ve aggregated into TimesTen matching what we’ve got in the source data on Oracle? Here was can use INACTIVE_SCHEMAS to force the BI Server to ignore TimesTen entirely. We can see from the nqquery.log that OBI has now gone back to the Oracle source of the data:

[...]
------------- Sending query to database named orcl
WITH
SAWITH0 AS (select sum(T117.FCAST_SAL_AMT) as c1,
     T127.MONTH_YYYYMM as c2
from
     GCBC_SALES.TIMES T127 /* Dim_TIMES */ ,
     GCBC_SALES.SALES T117 /* Fact_SALES */
[...]

and the report shows that actually we have a problem in our data, since what’s on the source doesn’t match the aggregate:

A Direct Database Request against Oracle confirms the data we’re seeing – we have a mismatch between our source and our aggregate:

This is the kind of testing that it is crucial to perform. Without proper testing, problems may only come to light in specific reports or scenarios, because by the very nature of aggregate navigation working silently and hidden from the user.

So this is the feature we can use to perform the testing, but below I demonstrate a much more flexible way that having to build multiple reports.

Using INACTIVE_SCHEMAS in your report is very simple, and doesn’t require modification to your reports. Simply use a Variable Prompt to populate INACTIVE_SCHEMAS as a Request Variable. Disable the Apply button for instantaneous switching when the value is changed.

A Request Variable will be prepended it to any logical SQL sent to the BI Server. Save this prompt in your web catalog, and add it to any dashboard on which you want to test the aggregate:

Even better, if you set the security on the dashboard prompt such that only your admins have access to it, then you could put it on all of your dashboards as a diagnostic tool and only those users with the correct privilege will even see it:

So far this is all negative , in that we are specifying the data source not to use. We can examine nqquery.log etc to confirm which source was used, but it’s hardly convenient to wade through log files each time we execute the report. Ripped off from Inspired by SampleApp is this trick:

1. Add a logical column to the fact table
2. Hard code the expression for the column in each Logical Table Source
3. Bring the column through to the relevant subject area
4. Incorporate it in reports as required, for example using a Narrative View.

Bringing it all together gives us this type of diagnostic view of our reports:

There’s a variety of ways to write bespoke test reports in OBI, but what I’ve demonstrated here is a very minimal way of overlaying a test capability on top of all existing dashboards. Simply create the Request Variable dashboard prompt, set the security so only admins etc can see it, and then add it in to each dashboard page as required.

In addition, the use of a ‘data source’ logical column in a fact table tied to each LTS can help indicate further where the data seen is coming from.

1. Re-create and restore the standby controlfile
2. Restore missing datafiles on the standby
3. Catalog standby database datafiles (diskgroup was different from primary)
4. Recover the database
5. Complete the rest of the standby configure to make it in sync 

I am sure some of you are doing Agile Scrum to manage your own software development. We do this as well for My Oracle Support Development. In the past I have talked about user research and touched on how we score issues we find or want to address. I thought in the spirit of the Agile world I would elaborate on this.

Here is the question...

My answer is that you score them. Scoring is more powerful than just an order and allows for a natural sort order (supporting the concept of doing "the most important" stories first, from the customers perspective). Scores can be compared between teams and in SAFe for getting stories that other teams need to complete for you, into a fair and manageable order (or at least to have the discussion). Whatever is "most important" is ranked first and should be done first. We do this rank ordering by scoring each item. Then the highest scores for features or bugs go first. This is more repeatable than just "moving things around" in your Agile tracking tool till it looks right.

See what you think...

First let me tell you about the wrong way...

Look at your list and see which ones you (as a Product Owner) think should be done first, maybe the ones where you know the developers can do it quickly.

The right way: Use an "unbiased" method to put the items in order using a three step rating system. This score could be generated by you or someone else. You do this by answering the following questions

How Many Users Does it Impact (3 - All, 2 - Some, 1 - A few or a Limited User Role)
How Often Does it Occur (3 - All of the Time, 2 - Some of the Time, 1 - Infrequently)
How Bad is the Problem (4 - Severe, 3 - Critical, 2 - Important, 1 - Minor Importance)

Take the "score" from each item and multiple it together (3x2x2=12). Now order your stores by the score. As new stories come in, score them. They will naturally fall into the right order.

By learning some simple rules, two independent people with a common understanding of the scores should be able to score the same item the same way. But just like playing "Poker" to come up with development time estimates for stories, there can be differences. Let someone else score the same story and if they come up with a different score, discuss why. Nine times out of ten you can easily resolve the difference and come to a common agreement. If you don't, use the higher score of the rating. Why? Because if there is confusion about the scope of the problem, you are likely underestimating it anyway. Scope rarely shrinks over time, so go conservative.

How Many Users Does it Impact (3 - All, 2 - Some, 1 - A few or a Limited User Role)

This score has to be done based on the total number of customers for the product. it canNOT be the number of people who use the specific feature in question. That is "everyone" signs in. So no question about rating something to do with Sign In a "3", but only a very limited number of people customize their home page. So something to do with that would be a "1". You can't change the scope and say, "well WITHIN the people who customize, ALL of them will use this feature". It doesn't work that way. You are trying to create a score that can be measured against other stories. So, one can easily see where the most good would come out of applying resources.

Examples of 3's for My Oracle Support (the product I spend most of my time thinking about)

• Sign In
• Issues on the Landing Page
• Searching Knowledge (because "everyone" does this)
• Viewing Trouble Tickets (we call them Service Requests or "SRs")
  
• My Settings

Examples of 2's

• Advanced Filters in Tables
• Editing SRs
• Creating SRs
• Creating On Demand RFCs (Request for Change to our on-demand serice)
  
• SR Profiles ("templates" used to file SRs)
  

Examples of 1's

• Customizing a Region
• Approve User (to access content)
  
• Help Link in a Feature used by a small audience
  

So you can see, it is sort of a the top 20% of use are 3's, the middle 80% are 2's and the bottom 20% are 1's.

How Often Does it Occur (3 - All of the Time, 2 - Some of the Time, 1 - Infrequently)

If every time you come here, the problem exists, it is easy to make this a "3". If it only happens in a specific mode or state (say when someone does a complex filter on a table THEN your region exhibits this problem), then it is a 2, and those annoying errors that pop-up rarely would be a "1". Of course, you have to judge if those "errors" are some of the time, or infrequent, because as you might expect when you multiply the values together multiplying by "1" doesn't do anything. ;-> So we do consider that "intermittent" errors that are difficult to reproduce, but you have personally seen them more than once would be a "2". Again you have some flexibility, but all of the time means all of the time, just use your judgement between 2's and 1's. I would say if it is less than 10% of the time, then it is a 1.

Examples of 3's

• Every time you open a dialog box it is empty
• A typo would be "all of the time".
• A scroll bar always appears even when not needed or wanted

Examples of 2's

• If your saved filter's name is too large then it truncates
• You get a time-out error after using the product for 10 minutes and clearly you have not timed-out (the time-out is say 4 hours)
  
• A dialog box appears off screen some of the time

Examples of 1's

• An error appears rarely and you have no idea why. It only happened once that session and everything seems to be working
  
• When you save an SR profile, on rare occasions it will error out
  
• Every once in a while, I go "Back" in the setup wizard or flow and it forgets that I completed a step and shows the wrong state for the step
  -- Remember this is just frequency, don't get freaked out because some of these appear to be bad issues, we should catch that next...
  

How Bad is the Problem (4 - Severe, 3 - Critical, 2 - Important, 1 - Not Important)

This is probably the easiest one for anyone to score. It is basically the inverse of Bug Severity. Likely you have something well understood in your organization. In our organization a true "Severity 1" issue doesn't come up that frequently. Severity 1 means "service down" - totally unavailable and no work around. We don't tend to see Sev 1's very often in development, because the code is not in production and the system is not down. But from a usability perspective, if I can't complete the task that is a usability "Sev 1" and thus is worth a score of 4. Likely these are just bugs. We have a long list of definitions of Prioritization of Bugs. I have this printed out by my desk so if I forget. We have a category one down from a "Sev 1" called a Sev 2 Showstopper. That too would get scored a 4.

You might already have a well tuned definition of what is in each severity in your organization, I would use that. But here are a few examples I would share.

4- Severe ("Sev 1" or P2 Showstoppers in my world)

• ADA: Major Accessibility issues, including missing labels, non-standard abbreviations, using only color to distinguish UI elements, etc. (These would be flagged as P1s by ADALint)
• NLS: Missing msg files causing pages not to render; garbled error msgs, can't translate the string
  
• Help doesn't come up
• Performance Issues (beyond our stated level of service)
  

3- Critical (typically a P2 in a bug system)

• UI: A significant percentage of users would need assistance to complete the task
• UI: Context lost during workflow
• UI: Typos
• Scalability issues (such as using a shuttle when it's likely that there will be thousands of elements)
• User isn't prevented from making a serious mistake.

2- Important (P3's)

• UI: A large number of users would need assistance to complete the task
• Hard to understand concepts
• UI: Layout is confusing.
• UI: Incorrect page header
• UI: Incorrect breadcrumbs (may be a P2 if severe because the user may lose context)
• UI: Incorrect time format
• UI: Grammatical errors

1- Minor Importance (P4s)

• UI: Minor inconsistencies (like button order, using Delete instead of Remove, using OK instead of Continue, missing units after numbers even if the value is obvious)
  
• UI: Incorrect usage of blank table cells versus N/A or unavailable.

Some discussion of this can be found in the Blog post.

Most tools can expose additional fields or columns. Typically you might have a Development Priority drop menu (1 to 4 is typical). This is similar.

I like to expose all 4 fields (the three scores and then the final score to sort by). This allows for discussion to validate the assumptions made. Like I said, you might have slight disagreements, and this brings it into the open to clarify. Expect to be able to use this to drive the sort order for your stories and bugs, so that the sprint teams, release management and your customers can see how and why this order exists. Transparency is best here.

Of course, you can go "all the way" and create a score so complex that no-one would really understand the difference between something with a score of "2032" and "2840". I know a system that has 17 factors, adds and subtracts based on who is escalating the issue (a VP escalating the issue is worth more than if I do it), how old the issue is, when it was filed, and its severity among the many factors. I just find that (and I would suspect some simple research into this would confirm) that mortals like me would have no chance in getting a good feel for working with the output of a score based on 17 factors. So I will approach this as a "Keep it Simple" method.

And it is true that this simple score will not differentiate enough when you have 20 stories all with a score of 12. Clearly you might want to either include more rules for ranking these scored items (a 12 that is for more customers (a 3 on that scale) is higher than a 12 coming from a worse bug that is for few customers (a 2). You decide. But the more complexity you add the more difficult it is to understand and judge differences. And you have to decide if it worth the additional complexity, confusion and overhead. I am minimally advocating that you do NOT just rank stories and and nor just score arbitrarily. Use a method that is repeatable and even can be consistently applied from team to team. So when you aggregate your rankings or your backlog you are comparing apples to apples.

Clearly we have more than one backlog. The scrum team for the sprint as a backlog, so does the project, maybe the program and even the release. But effectively these are virtual and clearly tagging an item for a sprint doesn't mean you can't look at it in the context of all other backlog items. I am suggesting that a single large backlog is sometimes useful to get a few key metrics out of, if done right. In Agile at scale you are looking at different roll-ups of stories. At the Program level you are looking at features that decompose into stories. If the feature has a score of 36, it won't mean that all stories in that feature will have the same value. Clearly as you break it down, some of the stories are more important than others.

The backlog at the Program or Product level might span say 30 or more teams, like it does for my organization. If a few of those teams represent 50% of the backlog (thinking now in terms of story points or work effort, not in terms of number of items), then maybe you should reconsider how you have allocated your teams. Looking at this Backlog across your teams and knowing the value completing those stories would bring to the customer helps you do this allocation. Cool!

Looking at it another way...

If you have one backlog, and use a scoring method, you can see how many "customer points" are being delivered by each team, compared to other teams. That is a team with 5 stories each with a score of 12, is delivering (5x12) 60 customer points in that sprint. While another team might be doing just three stories with scores of 36, 24, and 12, and thus delivering about the same (62 points) value to the customer. But even a team tackling 15 stories, if their summed value is 30, they are delivering 1/2 the value. Maybe a reallocation of teams to projects that have more customer points out there is warranted. This is not a challenge for a single sprint team working one list top down, but when doing Agile at Scale, one has to consider when to reallocate sprint resources to be the most effective. 

This is the same argument we had about moving away from counting bugs. If you lived in most development organizations it was bugs that were counted and reported. A team with 50 bugs should not be compared to a team with 3 bugs on how quickly they fixed those bugs or if it was worth the time and energy. The team with 3 bugs might have been doing something far more valuable and even more costly (development cost) than the team who has 50 "easy" bugs to fix. For design, we look at customer points. For development, in Agile, we can look at Story Points, or level of effort in days. Then we can look at this single virtual backlog based on scores and learn something valuable.

I might add, a backlog which is just ordered, is not as useful. We learned this in basis statistics. Ordinal numbers (1 comes before 2) are not as powerful and are not able to be given more thoughtful interpretation unless they are on an "interval" scale (where the difference between 2 numbers is of an equal interval). A score of 5 to 10 is effectively twice as important. But something just ranked 5th and 10th does not communicate this. Ten is at best 5 behind 5. That is, items 6, 7, 8 and 9 might have huge differences in value or might not. An interval scale explains this. A "score" is an interval scale. A ranking is not. This is why you now see College Football rankings also showing the points used to get that ranking. So now you know how far "behind" your team really is from the next spot up.

Please see this short discussion, or read his amazing book (I am still working through it, but if you like numbers and methods, this is so the book for you...) - Reinertsen, Don. Principles of Product Development Flow: Second Generation Lean Product Development. Celeritas Publishing, 2009.

I have not flushed out an answer for this. But I am leaning towards my scoring as a proxy for his numerator (relating User|Business Value, Time Criticality and Risk Reduction|Opportunity Enablement Value). With his use of a fibonacci type number and adding, there is more breadth in his scale. Likewise once divided it by job size it creates more spread. But my focus is on the requirements side, hence the numerator. I let development decide the denominator.

I am considering (and, you the reader) might want to weigh in, if I should weight my values more. Right now a Sev 1 is worth 4 points, only 33% more than a Sev 2 at 3 point, and only 50% more than a Sev 1 at 2 points.

I did not. I took this from a ranking method provided to me by Philip Haine, now at Success Factors, many years ago. And I also know that he got it from another well known UX expert. So it has been around the block a few times. I am just saying that this model can also be applied to Agile Scrum to help us all keep our Backlogs in priority order without resorting to magic.

This entry might seem a bit trivial, but from experience I know that sometime new features that are added to the product are not detected by developers who just continue working in the "traditional way". Well here is a quick update on such a feature - PPR:

Way back in 2009 I did a blog entry that showed how to implement partial page rendering (PPR) in ADF by setting the partialTrigger attribute of a field to depend on another.

Somewhere along the way* ADF got more advanced, and today there's a simpler way to do this without the need to define the partialTrigger property for your calculated field. Instead you just define dependency between fields in the model layer (ADF BC) and your View layer automatically handles the update to the screen. This is driven by the default use of the "ppr" mode for the ChangeEventPolicy of iterators in your page's binding layer.

Here is a quick demo that shows you how to define a calculated field that depends on the values of two other fields, and have it automatically display the value when the other fields are set.

For the record here is the bit of groovy code used in the calculated field:

if (CommissionPct != null)
{return Salary * (1+CommissionPct);}
else
return Salary

* - I'm not exactly sure in which version of JDeveloper this became the default behavior, but I just looked in 11.1.1.7 and the default changeEventPolicy is not PPR for a page - but it seems like you can change it to ppr to get it working.

Last night we all got together to eat some food and chat. Julian Dontcheff is practically a savant where Bulgarian Poetry, World Cup match results and random Oracle facts are concerned. Although Christian Antognini was pretty impressive on the random Oracle facts too.

I didn’t have any presentations today, so I got to sit and watch. I’ve done loads of typing, mostly of syntax for 12c features, but it’s not really stuff that is worth posting, because I have no way to validate it out, so I’m just going to keep it as a reminder for when I get hold of 12c and can try it out.

The sessions I went to included:

• Joze Senegacnik : Is my SQL Statement Using Exadata Features
• Christian Antognini : SQL New Features in the latest generation of Oracle Database
• Julian Dontcheff : Upgrading to the latest generation of database technology
• Christian Antognini : How the Query Optimizer Learns from its Mistakes
• Clive King : Solaris 11u1 performance and stability : features and frameworks
• Tom Kyte : Tom’s Top 12 Things about the Latest Generation of Database Technology

There was a lot of material I had seen at OOW2012 and UKOUG2012, but also a lot I had not, so I’m glad I went to them. The smaller setting also made it easier to ask questions, which can be quite daunting at the big events.

Tom gave me a couple of tips that have gone straight into one of my talks for tomorrow. I’m gonna have to name check him for it, or I’ll feel like I’m passing it off as my own.

I said this after OOW2013 and I’m sure I will say it again, but the number of changes in 12c is pretty daunting. I guess the fact it’s been about a 3 year wait, rather than the normal 18 months adds to that. In many cases (but not all) it’s not the scope of the individual changes that are the issue, but the sheer volume of them. I think people are going to be blogging for a long time before they’ve got through them. It will be interesting to see what gets selected for inclusion in the OCP DBA upgrade exam.

I’m off to dinner now. I will try to get some photos and post them in “Day 1 (part 2)@ tomorrow.

Cheers

Tim…

The call for papers is open for Tech 13 – the “server-side” conference of the UKOUG.

The conference was getting so big that we’ve split Apps from Server Tech and will be running the two conferences separately this year. The Server Tech conference will be in Manchester from 2nd to 4th Dec.

The closing date for submissions is Friday 31st May (only 2 weeks !) and confirmation of acceptance given by August.

There is a slightly shorter route to submission (if you don’t want to watch the video on “Why to speak”).

Cloud computing has grown from merely an IT trend to an essential technological initiative that is driving a competitive advantage for many enterprises. However, it has become clear from recent research that on the whole, firms are not effectively deploying these solutions. This means that in order to gain more value from cloud initiatives, companies may need to seek the support of remote database services.

Information Management contributor David Linthicum explained that private clouds have become increasingly common because firms are often more comfortable with this approach than moving sensitive data to the public cloud. And while these solutions can be valuable, he noted that most enterprises do not select the right applications and data for migration. By moving the wrong systems and information, he explained that companies are unable to derive the inherent benefits of cloud technologies.

A recent study by Claranet found that while companies are eagerly migrating web portals (67 percent), ecommerce web apps (61 percent), online marketing services (59 percent) and email (51 percent) to the cloud by the first quart of 2014, many are neglecting to move mission-critical systems. In fact, only 30 percent are planning to move IT asset management services, and just 32 percent are transferring finance applications to cloud servers. Michel Robert, managing director at Claranet, asserted that that with managed cloud migration of mission-critical apps, these projects can be both effective and secure.

Siloes obstruct value
A related Oracle report had similar findings that suggest challenges to cloud adoption. In fact, more than half of the 1,355 senior decision-makers surveyed admitted their department had experienced downtime in the last six months due to cloud integration issues, and the same number have missed deadlines as a result of these problems. Three-quarters of respondents even experienced an inability to innovate from isolated cloud apps integration. The failed promises of cloud projects have mainly resulted from business process siloes, according to Oracle. Three-quarters of businesses aimed to leverage quick access to software in the cloud, but only half of respondents have been able to access cloud data in other departments directly from within their designated business application.

"Subscribing to a cloud service may be relatively straightforward, but how this application fits in with the rest of the enterprise, including on-premise systems and other cloud applications must be thought through," said Rex Wang, Oracle vice president of product marketing.

Wang stressed that cloud apps have the potential to drastically minimize costs and drive business performance, but only if they can function effectively across the business.

RDX offers a full suite of cloud migration and administrative services that can be tailored to meet any customer's needs. To learn more our full suite of cloud migration and support services, please visit our Cloud DBA Service page or contact us.

The red carpet has been laid down at this Log Buffer Edition, and you can witness and cheer the cat-walking blog posts from Oracle, SQL Server and MySQL. Every one of them is chic, elegant, sensual in its own right. Enjoy.
Oracle:

Create colored heat maps in SQL*Plus with Kyle Hailey.

Here’s a quick and dirty script to create a procedure (in the SYS schema – so be careful) to check the Hakan Factor for an object.

Connor has a good post about default null for collection parameter.

This is yet another blogpost on Oracle’s direct path read feature which was introduced for non-parallel query processes in Oracle version 11.

Owen Allen has seen some questions about provisioning Oracle Solaris 11. They boil down to this.

SQL Server:

Shashank Srivastava tells us as how to Change the SQL Server Instance Name after Renaming the Windows Host.

Daniel Calbimonte shares as how to synchronize two SSAS Servers.

Data Architecture underpins just about everything we do in IT.  Without a clear understanding of how data is structured, there is no reliable way to derive meaning from it.

Orlando Colamatteo is login-less in Seattle.

Lets get started testing database with tSQLt with Robert Sheldon.

MySQL:

After a lot of fuzz, Anders Karlsson is now releasing MyQuery version 3.5.1.

Nothing like reestablishing a tradition and Dave Stokes is doing just that for MySQL.

Mare Alff is spreading the word about the performance schema.

Slava Akhmechet talks about secondary indexes, batched inserts performance improvements, soft durability mode.

It is a central part of the MySQL philosophy to try and help you as much as you can. There are many occasions when it could tell you that what you are asking for is utterly stupid or give you a bad execution plan because “you asked for it”.

Sometime back I was involved in a project which was to collect information for the servers we are supporting – creating inventory of servers. Logging into each server and collecting information is bit tedious when you have hundreds of server in your environment.  I have spent sometime and created a script that does this work for me, however, I still need to connect to the server – but using SSMS/Query Window and execute the script. And then the script will provide me the details I needed.

This inventory should have details like below for cluster and stand alone instances:

Server Name

OS Name

OS Edition

OS Patch Level

SQL Server IP

Is Clustered

Node1_Name

Node1_IP

Node2_Name

Node2_IP

SQL Server Edition

SQL Server Patch Level

Server Time Zone

SQL Server Version

SQL Server Platform

Processor Core

Physical Memory

Service Account Name

Domain

Looks good ?  Below is the version 1 of this script.

/*
IP address portion using : http://www.sqlservercentral.com/Forums/Topic150196-8-1.aspx
REMOVE sp_configure parameters if you are executing this script on SQL Server 2000

Created By : Hemantgiri S. Goswami | http://www.sql-server-citation.com
Date : 24th March 2013
Version : 1.0

Tested ON:
Windows Server: 2003, 2008, 2008 R2
SQL Server:2000, 2005, 2008, 2008 R2, 2012

*/
sp_configure 'show advanced options',1
RECONFIGURE WITH OVERRIDE
GO
sp_configure 'xp_cmdshell',1
RECONFIGURE WITH OVERRIDE
GO
DECLARE @TimeZone NVARCHAR(100)
 ,@ProductVersion SYSNAME
 ,@PlatForm SYSNAME
 ,@Windows_Version SYSNAME
 ,@Processors SYSNAME
 ,@PhysicalMemory SYSNAME
 ,@ServiceAccount SYSNAME
 ,@IPAddress SYSNAME
 ,@DOMAIN SYSNAME
 ,@MachineType SYSNAME
 ,@SQLServerIP VARCHAR(255)
 ,@CMD VARCHAR(100)
 ,@Node1 VARCHAR(100)
 ,@Node2 VARCHAR(100)
 ,@Node1IP VARCHAR(100)
 ,@Node2IP VARCHAR(100)
 ,@OSEdition VARCHAR(100)
 ,@OSVersion VARCHAR(100)
 ,@OSName VARCHAR(100)
 ,@OSPatchLevel VARCHAR(100)

CREATE TABLE #TempTable
 (
 [Index] VARCHAR(2000),
 [Name] VARCHAR(2000),
 [Internal_Value] VARCHAR(2000),
 [Character_Value] VARCHAR(2000)
 ) ;

INSERT INTO #TempTable
EXEC xp_msver;

-- Replace @Value_Name to N'TimeZoneKeyName' when running on Windows 2008
EXEC master.dbo.xp_regread
 @rootkey = N'HKEY_LOCAL_MACHINE',
 @key = N'SYSTEM\CurrentControlSet\Control\TimeZoneInformation',
 @value_name = N'StandardName',
 @value = @TimeZone output

EXEC master.dbo.xp_regread
 @rootkey = N'HKEY_LOCAL_MACHINE',
 @key = N'SYSTEM\CurrentControlSet\Services\MSSQLServer',
 @value_name = N'ObjectName',
 @value = @ServiceAccount output

EXEC master.dbo.xp_regread
 @rootkey = N'HKEY_LOCAL_MACHINE',
 @key = N'SYSTEM\CurrentControlSet\Control\ProductOptions',
 @value_name = N'ProductType',
 @value = @MachineType output

EXEC master.dbo.xp_regread
 @rootkey = N'HKEY_LOCAL_MACHINE',
 @key = N'SYSTEM\CurrentControlSet\Services\Tcpip\Parameters',
 @value_name = N'Domain',
 @value = @DOMAIN output

EXEC master.dbo.xp_regread
 @rootkey = N'HKEY_LOCAL_MACHINE',
 @key = N'CLUSTER\NODES\1',
 @value_name = N'NodeName',
 @value = @Node1 output

EXEC master.dbo.xp_regread
 @rootkey = N'HKEY_LOCAL_MACHINE',
 @key = N'CLUSTER\NODES\2',
 @value_name = N'NodeName',
 @value = @Node2 output

EXEC master.dbo.xp_regread
 @rootkey = N'HKEY_LOCAL_MACHINE',
 @key = N'SOFTWARE\Microsoft\Windows NT\CurrentVersion',
 @value_name = N'ProductName',
 @value = @OSName output

create table #OSEdition (VALUe varchar(255),OSEdition varchar(255), data varchar(100))
insert into #OSEdition
EXEC master.dbo.xp_regread
 @rootkey = N'HKEY_LOCAL_MACHINE',
 @key = N'SYSTEM\CurrentControlSet\Control\ProductOptions',
 @value_name = N'ProductSuite'
SET @OSEdition = (SELECT TOP 1 OSedition FROM #OsEdition)

EXEC master.dbo.xp_regread
 @rootkey = N'HKEY_LOCAL_MACHINE',
 @key = N'SOFTWARE\Microsoft\Windows NT\CurrentVersion',
 @value_name = N'CSDVersion',
 @value = @OSPatchLevel output

set @cmd = 'ping ' + @Node1
create table #Node1IP (grabfield varchar(255))
insert into #Node1IP exec master.dbo.xp_cmdshell @cmd
set @cmd = 'ping ' + @Node2
create table #Node2IP (grabfield varchar(255))
insert into #Node2IP exec master.dbo.xp_cmdshell @cmd

set @cmd = 'ping ' + @@servername
create table #SQLServerIP (grabfield varchar(255))
insert into #SQLServerIP exec master.dbo.xp_cmdshell @cmd

SET @SQLServerIP=(
 SELECT substring(grabfield, charindex('[',grabfield)+1, charindex(']',grabfield)-charindex('[',grabfield)-1)
 from #SQLServerIP where left(grabfield,7) = 'Pinging'
 )
SET @Node1IP =(
 SELECT substring(grabfield, charindex('[',grabfield)+1, charindex(']',grabfield)-charindex('[',grabfield)-1)
 from #Node1IP where left(grabfield,7) = 'Pinging'
 )

SET @Node2IP =(
 SELECT substring(grabfield, charindex('[',grabfield)+1, charindex(']',grabfield)-charindex('[',grabfield)-1)
 from #Node2IP where left(grabfield,7) = 'Pinging'
 )

SET @ProductVersion = (SELECT Character_Value from #TempTable where [INDEX]=2)
SET @Platform = (SELECT Character_Value from #TempTable where [INDEX]=4)
SET @Windows_Version= (SELECT Character_Value from #TempTable where [INDEX]=15)
SET @Processors = (SELECT Character_Value from #TempTable where [INDEX]=16)
SET @PhysicalMemory = (SELECT Character_Value from #TempTable where [INDEX]=19)
SELECT
 ServerName = @@SERVERNAME
 ,OSName = @OSName
 ,OSEdition = @OSEdition
 ,OSPatchLevel = @OSPatchLevel
 ,SQLServerIP = @SQLServerIP
 ,IsClustered = SERVERPROPERTY('IsClustered')
 ,Node1_Name = @Node1
 ,Node1_IP = @Node1IP
 ,Node2_Name = @Node2
 ,Node2_IP = @Node2IP
 ,SQLServerEdition = SERVERPROPERTY('Edition')
 ,SQLServerLevel = SERVERPROPERTY('ProductLevel')
 ,ServerTimeZone = @TimeZone
 ,SQLServerVersion = @ProductVersion
 ,SQLServerPlatform = @PlatForm
 ,ProcessorCore = @Processors
 ,PhysicalMemory = @PhysicalMemory
 ,ServiceAccountName = @ServiceAccount
 ,WKS_Server = @MachineType
 ,Domain = @DOMAIN

GO
DROP TABLE #Node1IP
DROP TABLE #NODE2IP
DROP TABLE #SQLServerIP
DROP TABLE #TempTable
DROP TABLE #OSEdition
GO
sp_configure 'xp_cmdshell',0
RECONFIGURE WITH OVERRIDE
GO
sp_configure 'show advanced options',0
RECONFIGURE WITH OVERRIDE
GO

Please do post back your feed back for this script, I will try my best to update and post back new version for this script.

- Hemantgiri S. Goswami (Cross posting from http://www.sql-server-citation.com/)

photo credit: Skimaniac via photopin cc

Hello again everyone.

Well it's Friday and we would like to wrap up this week by showcasing another one of our great partners, Team Informatics.  For those of you that caught the webcast yesterday featuring Mortenson Construction, you might remember that Team Informatics is the partner that assisted Mortenson in their very successful roll out of WebCenter Content and Portal.  If you have not taken the time to check out the webcast, you can do so at anytime by registering here and viewing it on-demand at your leisure.  And once you register for the webcast, you can also freely access the latest whitepaper entitled  "The Top 10 Criteria for Choosing an Enterprise Content Management System" as well as a previous webcast on the "Seven Ways Content Can Improve the Health of Your Business".  It's fun for the whole family!  

Here is a brief overview of Team Informatics that we hope you will find useful and informative. 

TEAM Informatics, Inc. (www.teaminformatics.com) is an employee-owned, Minnesota-based software products and systems integration firm with a global customer base. TEAM was formed over 10 years ago and has experienced a sustained aggressive growth rate with over 200 global customers.

TEAM is an Oracle Software Reseller and a global member of the Oracle Partner Network, specializing in areas such as WebCenter Content, WebCenter Portal and WebCenter Sites (formerly FatWire). Offerings include professional services, managed services, enterprise and development support, and an expanding set of products. In addition, TEAM is a Google Enterprise Partner and Reseller for the Google Search technologies. TEAM's suite of business applications include TEAM Cloud for managed delivery of WebCenter products on an Oracle engineered system, TEAM GSA Connector for enterprise search, TEAM Sites Connector for enabling web experience management, TEAM Email Manager for email and communication compliance and management, and ContentWorx for complex document process management and assembly. Get more information on these and all of TEAM's offerings at www.teaminformatics.com.

With TEAM’s growing customer base, they have been able to showcase and announce key customer successes, as well as nurture partnerships to provide best-in-class solutions. Some of their most recent announcements include:

TEAM Informatics Provides Strategic Roadmap for City of Las Vegas Oracle WebCenter Deployment

Engineered to Work Together – TEAM Informatics WebCenter Sites Connector v. 1.1 for WebCenter Content

TEAM Informatics Announces Full Life-Cycle, Paper to Paperless Solution, with Gill Digital

TEAM Informatics Announces Growth in North American Public Sector with Continuing Success in Oracle WebCenter

TEAM Informatics Announces Award Winning Project with Mortenson Construction

These announcements reflect TEAM’s on-going strategy of providing solutions that get the most out of software, providing all-in-one solution packages, and growing in each vertical with each successful project. Their commitment to their customers is only strengthened by their commitment to Oracle technology.

For more information, a demo, or to have a conversation with TEAM, please contact them via this link.

And in other news.... Are you a WebCenter Innovator?

With the emergence of IT trends such as cloud computing and mobility, enterprises have realized that previous security efforts are inadequate for mitigating the new risks that come with these technologies. While looking to upgrade the security infrastructure, firms are may need remote database support to ensure improvements in data protection.

The information security job market continues to expand. In fact, Help net Security revealed that according to a report by Burning Glass Technologies, over the past five years demand for cybersecurity professionals grew 3.5 times faster than that for other IT jobs. These needs will continue to grow as enterprises deploy new solutions that create vulnerabilities not previously dealt with. Help Net Security revealed that  ISACA International Vice President Allan Boardman believes the skill​ set for these jobs has also been evolving.

"Security professionals need to be knowledgeable about the main threats and issues related to key current technology trends, such as cloud services, social media, and consumerization of IT, including BYOD," Boardman told the source. "They also need to be well-versed in data privacy and data protection, particularly if they are in financial services or healthcare. It is highly desirable to have strong technical skills, including security architecture and forensics skills."

Additionally, Eric Presley of CareerBuilder explained to Help Net Security that while information security analysts are among the most sought-after professionals, there is a rising need for network architects and engineers that can manage security protocols, particularly in the realm of healthcare.

Filling the gap
In an interview with BankInfoSecurity, Boardman asserted that there is a considerable shortage of IT security skills. He pointed out that cybersecurity is a top priority for organizations in all industries, but the required knowledge has become more specific.

"Some of the gaps I would see specifically are around providing specific security guidance to organizations to address the issues in the current topical areas like big data, cloud security and mobile computing," Boardman explained to BankInfoSecurity. "Those are all topics where people need more detailed specific guidance and tools to be able to help them through that."

It can be difficult to find professionals that have the technical and architecture skills, such as database experts and security specialists. By seeking third-party support, enterprises can leverage the required knowledge for safeguarding sensitive information.

RDX's highest priority is safeguarding customer information. To learn more about how RDX ensures data security, please visit our Focus on Security page or contact us.

Offloading slow batch tasks to an external process is a common method of improving website responsiveness. One great way to initiate such background tasks in PHP is to use Oracle Streams Advanced Queuing in a producer-consumer message passing fashion. Oracle AQ is highly configurable. Messages can queued by multiple producers. Different consumers can filter messages. From PHP, the PL/SQL interface to AQ is used. There are also Java, C and HTTPS interfaces, allowing wide architectural freedom.

The following example simulates an application user registration system where the PHP application queues each new user's street address. An external system monitoring the queue can then fetch and process that address. In real life the external system might initiate a snail-mail welcome letter, or do further, slower automated validation on the address.

The following SQL*Plus script qcreate.sql creates a new Oracle user demoqueue with permission to create and use queues. A payload type for the address is created and a queue is set up for this payload.

-- qcreate.sql

connect / as sysdba
drop user demoqueue cascade;

create user demoqueue identified by welcome;
grant connect, resource to demoqueue;
grant aq_administrator_role, aq_user_role to demoqueue;
grant execute on dbms_aq to demoqueue;
grant create type to demoqueue;

connect demoqueue/welcome@localhost/orcl

-- The data we want to queue
create or replace type user_address_type as object (
  name        varchar2(10),
  address     varchar2(50)
);
/

-- Create and start the queue
begin
 dbms_aqadm.create_queue_table(
   queue_table        =>  'demoqueue.addr_queue_tab',
   queue_payload_type =>  'demoqueue.user_address_type');
end;
/

begin
 dbms_aqadm.create_queue(
   queue_name         =>  'demoqueue.addr_queue',
   queue_table        =>  'demoqueue.addr_queue_tab');
end;
/

begin
 dbms_aqadm.start_queue(
   queue_name         => 'demoqueue.addr_queue',
   enqueue            => true);
end;
/

The script qhelper.sql creates two useful helper functions to enqueue and dequeue messages:

-- qhelper.sql
-- Helpful address enqueue/dequeue procedures

connect demoqueue/welcome@localhost/orcl

-- Put an address in the queue
create or replace procedure my_enq(name_p in varchar2, address_p in varchar2) as
  user_address       user_address_type;
  enqueue_options    dbms_aq.enqueue_options_t;
  message_properties dbms_aq.message_properties_t;
  enq_id             raw(16);
begin
  user_address := user_address_type(name_p, address_p);
  dbms_aq.enqueue(queue_name         => 'demoqueue.addr_queue',
                  enqueue_options    => enqueue_options,
                  message_properties => message_properties,
                  payload            => user_address,
                  msgid              => enq_id);
  commit;
end;
/
show errors

-- Get an address from the queue
create or replace procedure my_deq(name_p out varchar2, address_p out varchar2) as
  dequeue_options    dbms_aq.dequeue_options_t;
  message_properties dbms_aq.message_properties_t;
  user_address       user_address_type;
  enq_id             raw(16);
begin
  dbms_aq.dequeue(queue_name         => 'demoqueue.addr_queue',
                  dequeue_options    => dequeue_options,
                  message_properties => message_properties,
                  payload            => user_address,
                  msgid              => enq_id);
  name_p    := user_address.name;
  address_p := user_address.address;
  commit;
end;
/
show errors

The script newuser.php is the part of the PHP application that handles site registration for a new user. It queues a message containing their address and continues executing:

<?php
// newuser.php

$c = oci_connect("demoqueue", "welcome", "localhost/orcl");

// The new user details
$username = 'Fred';
$address  = '500 Oracle Parkway';

// Enqueue the address for later offline handling
$s = oci_parse($c, "begin my_enq(:username, :address); end;");
oci_bind_by_name($s, ":username", $username, 10);
oci_bind_by_name($s, ":address",  $address,  50);
$r = oci_execute($s);

// Continue executing
echo "Welcome $username\n";

?>

It executes an anonymous PL/SQL block to create and enqueue the address message. The immediate script output is simply the echoed welcome message:

Welcome Fred

Once this PHP script is executed, any application can dequeue the new message at its leisure. For example, the following SQL*Plus commands call the helper my_deq() dequeue function and displays the user details:

-- getuser.sql

connect demoqueue/welcome@localhost/orcl

set serveroutput on
declare
  name varchar2(10);
  address varchar2(50);
begin
  my_deq(name, address);
  dbms_output.put_line('Name     : ' || name);
  dbms_output.put_line('Address  : ' || address);
end;
/

The output is:

Name     : Fred
Address  : 500 Oracle Parkway

If you instead want to check the queue from PHP, use getuser.php:

<?php
// getuser.php

$c = oci_connect("demoqueue", "welcome", "localhost/orcl");

// dequeue the message
$sql = "begin my_deq(:username, :address); end;";
$s = oci_parse($c, $sql);
oci_bind_by_name($s, ":username", $username, 10);
oci_bind_by_name($s, ":address", $address, 50);
$r = oci_execute($s);

echo "Name     : $username\n";
echo "Address  : $address\n";

?>

If the dequeue operation is called without anything in the queue, it will block waiting for a message until the queue wait time expires.

The PL/SQL API has much more functionality than shown in this overview. For example you can enqueue an array of messages, or listen to more than one queue. Queuing is highly configurable and scalable, providing a great way to distribute workload for a web application. Oracle Advanced Queuing is available in all editions of the database. More information about AQ is in the Oracle Streams Advanced Queuing User's Guide.

Bootnote: The basis for this blog post comes from the Underground PHP and Oracle Manual

Of course for all our customers that use ksplice and enjoy the cool zero downtime patching, theyt might not even have noticed if they ran *as many do* ksplice in automated mode or others just had to issue one single very simple command and they were done. No applications to bring down, no systems to reboot... and still safe, secure, patched, current.

some more specifics on the ksplice blog here.

There's also Time to release. The ksplice patch was available on Tuesday (5/14) while the RPM for the kernel was released on Thursday (5/16) by us and the other similar distributions. No hassle...