Just had a quick random thought on a use case I have in an integration. What I describe here is overkill and inappropriate in my case but it seems interesting enough to describe since it could be useful someday.
The general problem is one where I need to classify data into one of two buckets where the primary processing operates on one bucket. The classification is over a field tracking the number of times a particular condition occurs. The mapping of that count to the buckets, right now, is a simple comparison ( x <= y). Instead of simply embedding the comparison in the selection query and using a standard b-tree style index I decided to consider what else could be done.
First I found a method of mapping the count that I thought was interesting:
DECODE(SIGN(10-"CONDITION_CNT"),1,1,0)
In this example, I am mapping condition counts of 9 or less to the "1" bucket and everything else to the "0" bucket. The condition count is always >= 0 therefore the subtraction guaranteed a positive result for counts 0 to 9. The sign func returns 1 for anything >0 and 0 for an argument of 0 and -1 for anything less than 0. The decode maps the 1 to 1 and condenses everything else to 0. And there is the data determining the correct bucket..
After that, I was able to generate a bitmap index over that set of function calls. A bitmap index was chosen solely due to the cardinality.
CREATE BITMAP INDEX CONDITION_CNT_IDX ON SOME_TABLE (DECODE(SIGN(10-"CONDITION_CNT"),1,1,0));
No, this doesn't really help me but I still found it interesting. If I had some spare time I would try to do some sort of performance comparison just "to know". Unfortunately, spare time pretty much doesn't exist. A big reason this doesn't help me right now is my dataset is not very big. And even if it was, my integration system would be a performance holdup before query access was ever a problem.
Hope you found this of some interest.
Scott
Software Development, family, religious, hobby, fun and humorous items.
Showing posts with label Oracle. Show all posts
Showing posts with label Oracle. Show all posts
Wednesday, September 16, 2015
Saturday, September 12, 2015
Active Directory / Oracle - time stamp handling dilema
I recently received another last minute development request. I'm going to be a little bit vague on some details on purpose - some things can't be shared.
The general problem to solve is: On a particular administrative action, a user must complete a specific activity within a particular time frame. If that activity isn't completed then some data is manipulated to force the user to complete the activity in a timely fashion. If the activity was completed then flags are cleared regarding the condition.
On first pass through prototyping a possible solution, I recognized it isn't quite as straight forward as hoped.
In this case, we handle several pieces of information from different sources (Oracle and Active Directory). One item is an Oracle date/time (sysdate) generated by the administrative transaction. We save that "transaction timestamp" in Oracle along with a "to be done by" date which is also stored in Oracle. At that same time, an Active Directory field is indirectly updated to the equivalent of "now". This part of the process works ok and there are no real alternatives available at this time.
At this point in the process, an integration runs which looks for administrative transactions that passed or are at the "to be done by" date and therefore should be checked against the user activity to verify they completed their activity. This involved comparing an Oracle date/time stored in Oracle against Oracle sysdate - which works well enough and has no issue.
Next we get the user specific last transaction timestamp from Active directory which is represented in Active Directory as a "100ns increment from midnight Jan. 1, 1601". We then normalize the Oracle transaction timestamp to the same representation as the Active Directory timestamp. Now in a perfect world, if the Active Directory timestamp is newer than the Oracle transactions timestamp - then the user completed what was required and we clear flags and complete the transaction.Otherwise, we flag the user to complete their task.
The are 2 basic problems though; (1) there is an inherent difference between the Oracle date/time and the Active Directory timestamp - possibly due to one or more causes [activities are only semi-coordinated across servers, possible differences in available precision between Oracle date/time and AD timestamp]. (2) There can be minor differences between system times even when using NTP.
The result of these issues is that in some common circumstances, a user is determined to have completed the transaction just because of the time differences occurring because activities are serialized across different systems but using each systems time. This I can easily see in the test data I generated. I have not knowingly run into an issue with differences in the actual clocks in this current situation but we have had previous problems with clocks being out-of-sync.
The "cost" in this situation is significantly higher than desirable if users are determined to be "incomplete" when they actually are "complete". On that same note, for other reasons it is in the organizations best interest to be as accurate as reasonable.
[edit 2015/10/03]
Sourcing the time stamps only from AD isn't possible - initially I thought maybe it could be. The final solution isn't too hard to implement. First I had to determine how close my times had to be to meet business needs. In this case, I determined that the one use case affected would be fine with 10 seconds of accuracy. The way I implemented that was to take the transaction time and activity time and subtract them. If you take the absolute value of that and compare it against 10 seconds, I know whether the user met the timing requirement. Problem solved. If the 10 second value is externally configurable, I can easily update the behavior as business requirements change.
The general problem to solve is: On a particular administrative action, a user must complete a specific activity within a particular time frame. If that activity isn't completed then some data is manipulated to force the user to complete the activity in a timely fashion. If the activity was completed then flags are cleared regarding the condition.
On first pass through prototyping a possible solution, I recognized it isn't quite as straight forward as hoped.
In this case, we handle several pieces of information from different sources (Oracle and Active Directory). One item is an Oracle date/time (sysdate) generated by the administrative transaction. We save that "transaction timestamp" in Oracle along with a "to be done by" date which is also stored in Oracle. At that same time, an Active Directory field is indirectly updated to the equivalent of "now". This part of the process works ok and there are no real alternatives available at this time.
At this point in the process, an integration runs which looks for administrative transactions that passed or are at the "to be done by" date and therefore should be checked against the user activity to verify they completed their activity. This involved comparing an Oracle date/time stored in Oracle against Oracle sysdate - which works well enough and has no issue.
Next we get the user specific last transaction timestamp from Active directory which is represented in Active Directory as a "100ns increment from midnight Jan. 1, 1601". We then normalize the Oracle transaction timestamp to the same representation as the Active Directory timestamp. Now in a perfect world, if the Active Directory timestamp is newer than the Oracle transactions timestamp - then the user completed what was required and we clear flags and complete the transaction.Otherwise, we flag the user to complete their task.
The are 2 basic problems though; (1) there is an inherent difference between the Oracle date/time and the Active Directory timestamp - possibly due to one or more causes [activities are only semi-coordinated across servers, possible differences in available precision between Oracle date/time and AD timestamp]. (2) There can be minor differences between system times even when using NTP.
The result of these issues is that in some common circumstances, a user is determined to have completed the transaction just because of the time differences occurring because activities are serialized across different systems but using each systems time. This I can easily see in the test data I generated. I have not knowingly run into an issue with differences in the actual clocks in this current situation but we have had previous problems with clocks being out-of-sync.
The "cost" in this situation is significantly higher than desirable if users are determined to be "incomplete" when they actually are "complete". On that same note, for other reasons it is in the organizations best interest to be as accurate as reasonable.
[edit 2015/10/03]
Sourcing the time stamps only from AD isn't possible - initially I thought maybe it could be. The final solution isn't too hard to implement. First I had to determine how close my times had to be to meet business needs. In this case, I determined that the one use case affected would be fine with 10 seconds of accuracy. The way I implemented that was to take the transaction time and activity time and subtract them. If you take the absolute value of that and compare it against 10 seconds, I know whether the user met the timing requirement. Problem solved. If the 10 second value is externally configurable, I can easily update the behavior as business requirements change.
Sunday, September 21, 2014
Jenkins and Groovy - ERP data quality and validation
Many organizations have an ERP and they may manage large amounts of data. I won't bother debating the tradeoffs of commercial ERP systems but I want to mention some ways I am dealing with certain data issues.
Our ERP is one that has some pretty old roots (and they show their grey) - with the changes in technology, schema, functionality, etc there are plenty of issues in data validation by the system. Each new vendor maintenance release is like rolling the dice in regards to what validations change (for better or worse). On top of that, the vendor process for major upgrades doesn't really handle conversion and/or reporting of some data and it is usually tables which really need extra work. There are also business process and training issues within the distributed organization. All of this results in many data messes.
Data issues on their own are bad enough but the way the problems tend to manifest for us provide a very bad experience for our customers. In our case, the vendor technology manages data in a way where some data which is not actively being used (i.e. by an end user) is still operated on and validated as part of some transaction. The problem is that when the actively used/accessed data is saved, the system also performs validations on the non-actively used data. If that non-actively used data is invalid then the system throws a tantrum which usually has no useful message for the end-user since the data at fault is not in anyway related to what they did. This is especially true when you have a custom application accessing the system - useful error responses from the source system are a rarity.
So what do you do?
I am slowly putting Jenkins jobs which perform checks and validations for some of the common problems. I write the jobs to identify bad data, form a report of some sort and email it to particular users whom I hope are able to direct it to an appropriate person for correction/mitigation, etc.
The solution I am documenting here is the use of Jenkins with Groovy using JDBC to perform a check, report generation and emailing of any failure result.
I am modifying things somewhat to take organization identifying type information out.
Our ERP is one that has some pretty old roots (and they show their grey) - with the changes in technology, schema, functionality, etc there are plenty of issues in data validation by the system. Each new vendor maintenance release is like rolling the dice in regards to what validations change (for better or worse). On top of that, the vendor process for major upgrades doesn't really handle conversion and/or reporting of some data and it is usually tables which really need extra work. There are also business process and training issues within the distributed organization. All of this results in many data messes.
Data issues on their own are bad enough but the way the problems tend to manifest for us provide a very bad experience for our customers. In our case, the vendor technology manages data in a way where some data which is not actively being used (i.e. by an end user) is still operated on and validated as part of some transaction. The problem is that when the actively used/accessed data is saved, the system also performs validations on the non-actively used data. If that non-actively used data is invalid then the system throws a tantrum which usually has no useful message for the end-user since the data at fault is not in anyway related to what they did. This is especially true when you have a custom application accessing the system - useful error responses from the source system are a rarity.
So what do you do?
I am slowly putting Jenkins jobs which perform checks and validations for some of the common problems. I write the jobs to identify bad data, form a report of some sort and email it to particular users whom I hope are able to direct it to an appropriate person for correction/mitigation, etc.
The solution I am documenting here is the use of Jenkins with Groovy using JDBC to perform a check, report generation and emailing of any failure result.
I am modifying things somewhat to take organization identifying type information out.
import groovy.text.SimpleTemplateEngine
import javax.naming.*
import javax.sql.DataSource
import javax.mail.internet.InternetAddress
import javax.mail.internet.MimeMessage
import javax.mail.*
import javax.activation.*
import groovy.sql.Sql
import static java.util.Calendar.*
class PlanValidations
{
// Produce a connection to the database
def connect()
{
Context initCtx = new InitialContext();
DataSource sqlDS = initCtx.lookup("jdbc/connid");
def sql = new groovy.sql.Sql(sqlDS);
return sql;
}
// An array to contain the column names returned by the query
def colnames = []
def checkPlanInactivation(sql)
{
// The query is looking for a misconfiguration which is typically a failure to follow the proper business process.
// The correct process for disabling the data item is add a new row with a current effective date which states which
// period the data should be disabled starting at. The query finds each max effective item plus several "lagging" fields for which an outer query compares to determine if the proper setup was used.
def badInactivePlanSQL = "
SELECT *
FROM
(SELECT INSTITUTION,
FIRST_PERIOD_VALID,
PROG,
PLAN,
LAST_PERIOD,
LAG(LAST_PERIOD, 1, ' ')
OVER (PARTITION BY INSTITUTION, PROG, PLAN ORDER BY EFFDT ASC)
PREV_LAST_PERIOD,
EFF_STATUS,
LAG(eff_status, 1, ' ')
OVER (PARTITION BY INSTITUTION, PROG, PLAN ORDER BY EFFDT ASC)
PREV_eff_status,
effdt,
LAG(effdt, 1, NULL)
OVER (PARTITION BY INSTITUTION, PROG, PLAN ORDER BY EFFDT ASC)
PREV_eff_dt
FROM PLAN_TBL
ORDER BY INSTITUTION,
PROG,
plan,
effdt
)
WHERE EFF_STATUS = 'I'
AND prev_eff_status = 'A'
AND prev_PERIOD = ' '
AND EFFDT > SYSDATE
";
// take all the results and send off an enail per institution
// This will be a map of lists {inst -> [rows]}
def institutionBadSetup = [:].withDefault {[]}
sql.eachRow(badInactivePlanSQL,
// keep track of the column names returned by the query
{ meta ->
(1..meta.columnCount).each { colnames << meta.getColumnName( it ).toLowerCase() }
},
// save off the bad data by institution
{
institutionBadSetup[it.INSTITUTION] << it.toRowResult()
}
)
return institutionBadSetup
}
def doChkInactivations()
{
def conn = connect();
def invalidPlanSetups = checkPlanInactivation(conn)
invalidPlanSetups.each() {
institution, lst -> sendEmail(getEmailTo(institution, conn), getSubject(institution), getMsg(institution, lst))
}
conn.close();
}
// Produce an html message with description of problem, related data and hints at fixing.
def getMsg(institutionId, plans)
{
def html = """\
<html>
<head></head>
<body>
There is a problem with the setup of following plans for $institutionId.<br/><br/>
*** Provide a message here which describes the problem and provides help in correcting. ***
<br/><br/>
"""
// use the saved off column names and match up with the field data while constructing the simple report
plans.each() { plan ->
(0..colnames.size()-1).each {
println plan.PLAN + " " + it
html += "${colnames[it]}[${plan[it]}] "
}
html +="<br/><br/>"
}
html += """\
</body>
</html>""";
return html
}
// Produce a subject for the email
def getSubject(institutionId)
{
def env = System.getenv()
String environ = env['ENV_TYPE']
String hostname = env['HOSTNAME']
return "invalid INACTIVE plan setup - institution(${institutionId}) <system name>(${environ}) server(${hostname})"
}
// Use an existing table of users to provide i
def getEmailTo(institutionId, sql)
{
// hard coded a user [me] as email "to" during R&D
def emailSql = "select 'me@someplace.org' as cntct_eml from inst_misc where institution = '${institutionId}'"
def emailAdr = sql.firstRow(emailSql)
return emailAdr.vx_oa_admcntct_eml
}
def sendEmail(receiver_email, email_subject, msgtext)
{
def reportdt = String.format('%tY/%<tm/%<td %<tH:%<tM:%<tS', Calendar.instance);
println "$msgtext";
Properties mprops = new Properties();
mprops.setProperty("mail.transport.protocol", "smtp");
mprops.setProperty("mail.host", "smtp.somewhere.org");
mprops.setProperty("mail.smtp.port", "25");
Session lSession = Session.getDefaultInstance(mprops, null);
MimeMessage msg = new MimeMessage(lSession);
msg.setText(msgtext, "utf-8", "html");
def me = "sender@somewhere.org";
StringTokenizer tok = new StringTokenizer(receiver_email, ";");
ArrayList emailTos = new ArrayList();
while(tok.hasMoreElements())
{
emailTos.add(new InternetAddress(tok.nextElement().toString()));
}
InternetAddress[] to = new InternetAddress[emailTos.size()];
to = (InternetAddress[]) emailTos.toArray(to);
msg.setRecipients(MimeMessage.RecipientType.TO,to);
msg.setSubject(email_subject);
msg.setFrom(new InternetAddress(me));
//Send the message via our own SMTP server, but don't include the envelope header.
Transport transporter = lSession.getTransport("smtp");
transporter.connect();
transporter.send(msg);
}
}
validationChk = new PlanValidations();
validationChk.doChkInactivations();
Subscribe to:
Posts (Atom)