Re: [sqr-users] Thinking about trying something - looking forideas

["Jamie Harris" <JHarris@frederick.edu>]

Arun,

That's not entirely true.  If Peoplesoft does everything correctly,
then yes there should never be new orphans.  But Peoplesoft is a very
big, complex system, and even the best testing on their end doesn't mean
there aren't bugs.  There are situations where orphans do happen.   For
example, if you term-activate a student and their academic standing gets
calculated (which happens immediately if you have that setup), then you
delete the term-activation row (using the Peoplesoft page, not SQL)
their academic standing row for that same term is left in place.  This
causes the "no matching buffer found for level" error when you pull up
the Term History page for that student.   That's because a row exists in
PS_ACAD_STDNG but not in the parent PS_STDNT_CAR_TERM.  This is
Peoplesoft-delivered functionality here, not some back-door data import.
 This kind of bug is hard to catch in testing because it's so rare that
anyone deletes a row in that table that people don't always think to
test what happens when you do.  Note: this may be fixed by now, I
haven't checked in a while and like I said, it's rare that people delete
rows from that table.   Bugs like that do happen, and you want to know
when a new one has sprung up before it's a lot of rows to deal with.  
You never know what's going to break in that latest patch bundle and
users don't always take the time to test as thoroughly as they should
before such patches are moved into production.  


As for an orphan-finding program I've found such a thing much easier in
Perl using the DBI modules.   Perl is great with the dynamic SQL kinds
of things (like select * from table, etc.)  - my Perl is about 100 lines
of actual code not counting the lines that handle the user input and/or
reading from command line parameters (I don't run it inside of
Peoplesoft).  But in SQR it's certainly do-able - especially using the
technique others have mentioned where one SQR writes code into an .SQC
that a second SQR runs (so you avoid all the dynamic SQL).  You can
queue those together into a Peoplesoft job and won't need Perl at all. 


The hard part about the Peoplesoft database (at least from what I've
seen - on SQL server) is that it doesn't set up any foreign key
constraints in the tables, it lets the user interface take care of all
that based on the pages.   So the only way to really know what table is
a child of what other table is to select from PSRECDEFN and use the
parent table listed there.  Then you can ask the database server what
the keys are in the two tables, figure out which keys they have in
common, etc.  

Sometimes the parent table listed in PSRECDEFN is wrong, however, so an
orphan finder done this way will return some false positives.  But you
know it's a false positive when it tells you that EVERY row in that
table is an orphan.  The peoplesoft pages are what really control the
relationships, if table A is scroll level 1 and table B is scroll level
2 that's what makes it treat table A as table B's parent, so the "parent
record" field in PSRECDEFN is meant as merely a convenience for
developers as far as I can tell (which is why sometimes it's wrong and
they haven't fixed it).   


I'm not bashing Peoplesoft here, I'm just saying that in a system with
over 16,000 tables it's hard to keep out the bugs sometimes, and that
includes creation of orphans.  It's nice to have a program that says you
have no orphans, and then the next week it tells you that you still have
none.   That's much better than having a line down the hallway and have
students who you can't help because somewhere in their data is an orphan
and the cashier (who doesn't know what "no matching buffer found for
level" even means yet) has to tell them to come back later.  



-----------------------------------------------------
James Harris
Enterprise Application Analyst/Programmer
Information Technology Division
Frederick Community College 

-----------------------------------------------------

>>> Arun Kumar <arunkumar13@yahoo.com> 1/4/2007 1:35 PM >>>
I am not sure about this.

If all the data comes in to the PS database thru its front end then
orphan rows are impossible to create in first place.

Orphans row exists because of data imported thru back doors and most of
the time during the initial conversion.

Once you fixed those process you will never have orphans, right?

Just my 2 cents.



----- Original Message ----
From: "Overcashier, Patricia" <Patricia.Overcashier@aam.com>
To: This list is for discussion about the SQR database
reportinglanguage from Hyperion Solutions. <sqr-users@sqrug.org>
Sent: Thursday, January 4, 2007 1:15:33 PM
Subject: RE: [sqr-users] Thinking about trying something - looking for
ideas

John,
This sounds like it would be a very useful sqr.  Could you please pass
on any suggestions you may get.  I'm interested in looking for orphans
too and agree that it is easy to imagine and put together an sqr to do
that, using dynamic sqls selecting from the tools tables, but also
agree
that it would take forever to run.  
Thanks,
Patti

-----Original Message-----
From: sqr-users-bounces+patricia.overcashier=aam.com@sqrug.org 
[mailto:sqr-users-bounces+patricia.overcashier=aam.com@sqrug.org] On
Behalf Of john.tucker@oracle.com 
Sent: Thursday, January 04, 2007 1:06 PM
To: sqr-users@sqrug.org 
Subject: [sqr-users] Thinking about trying something - looking for
ideas

I have a rather different question today.  I am thinking about writing
an SQR that would look for orphan data anywhere in a PeopleSoft
database, using PeopleTools tables to figure out what to look at
rather
than using a static list of tables.  I have an idea or two about how
to
go about this... but I wondered if anyone out here had already written
something similar and/or had some suggestions for things to do/avoid.

For example, there will be a lot of dynamic SQL getting processed in
this SQR.  Does that usually stop indexes from being used?  If so, is
there any way I can force indexes to be used?

Also, would it be better to say "select [whatever] from [child] where
not exists ([whatever] in [parent])" or would it be better to write
two
select statements, pulling all rows from the child table and trying to
match them with rows in the parent table in a separate select
statement?
Either way, I already know this thing is going to take forever to run.

I don't want you fine folks to do my work for me, but I was pretty
sure
someone out there would have suggestions on how I can make this a
better, faster tool than the way I would have written it without
asking.

-John T.


_______________________________________________
sqr-users mailing list
sqr-users@sqrug.org 
http://www.sqrug.org/mailman/listinfo/sqr-users 

_______________________________________________
sqr-users mailing list
sqr-users@sqrug.org 
http://www.sqrug.org/mailman/listinfo/sqr-users 






__________________________________________________
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around 
http://mail.yahoo.com 

_______________________________________________
sqr-users mailing list
sqr-users@sqrug.org 
http://www.sqrug.org/mailman/listinfo/sqr-users

_______________________________________________
sqr-users mailing list
sqr-users@sqrug.org
http://www.sqrug.org/mailman/listinfo/sqr-users