Panda + Community – September 2016

Okay so you might be saying are those Panda’s dancing? Yes, yes they are. This Dancing Panda video by hongmogu on youtube is awesome! So you might be asking why are the Panda’s dancing? Well, that is a really good question, I honestly didn’t think Panda’s could dance, but it turns out they can.

As part of our local hospital here in St Thomas, they are in the process of raising funds to expand and grow to meet all the needs of our local community. Wade and Lisa have been asked to dance with Ian and Jennifer in the open dance number at the Hospital’s Gala Night event.  So in addition to working an insane amount of hours the past 6 weeks, the dance floor has been busy trying to show a Panda with absolutely NO rhythm how to move his feet to music and actually not look like a bumbling idiot doing so.  (Not convinced it is working).

In addition we are busy gearing up for Christmas – yes I know – but literally it is just 2 months away. Considering how fast the months are going now that is about 6 blinks away. Oh wait, 5, I just blinked.  The Panda’s will be assisting Christmas Care this year, as Wade has joined the border of Christmas Care and will be working hard to sell Bears-4-Christmas-Care again this year.  This year The Smart Panda Corporation is going to try and bring back an old tradition that has been missing in the hampers for the past several years – Apples.  Yes Apples!  What Christmas isn’t complete without an apple in your stocking.  There appears to be all sorts of theories behind the giving of apples and/or oranges at Christmas, but for me it just reminds me of Christmas morning opening your stocking.  We are trying to get 5000 pounds of apples that will be packaged up and put in each of the 1500 hampers.

Speaking of Apples, Eat-2-Learn which is a program here in our community that helps ensure that kids from 38 different schools have food to eat so that they can learn at school.  The eat-2-learn program is incredible offering up breakfasts, lunches, and snacks to kids from kindergarten to teenage high-school students.  The best part of the program is it isn’t limited to one kid, it is for the entire school.  This inclusion helps make sure that all kids have the right food to learn. Our Rotary Club here has helped with the program at several schools and we were recognized at the Principal’s breakfast in September where each of the schools got their funding to ramp up the programs for the school year. My kids love the program and I know the kids that are actually hungry love it too.  There is some pretty amazing statistics around the program when it comes to the power of food in regards to learning – It really makes a difference.

Lastly, it may have been stupid warm here today, but soon enough the cold weather will be back upon us, and we are gearing up for another season working the kitchen at Inn-Out-Of-The-Cold serving up some hot delicious food to some of locals that need a little help during those cold months.

Until Next Month…… (I honestly can’t find any pictures —– next month we will have some!)

Kill Application Engine (AE)

It amazes me that in the world of these high end databases that simple data handling can go for a crap simply because data was bulk loaded into the system.  Usually during big changes to an environment: data conversions, upgrades,  archiving or other aggressive changes, data will become out of sync with the statistics that are stored on the tables.

What are Database Statistics? In a nut shell it is data about data or  simply metadata. Oracle statistics is metadata about your data. There are several kinds of statistics in Oracle mainly: Object statistics, System statistics and fixed table statistics.

So if you have bad statistics your Application Engine may not be able to execute code correctly.  It will simply appear to be hung.  I have seen processes run for days and it will never return a result because of bad statistics.  After updating the statistics those exact same processes that ran for days will run in seconds, sometimes faster.  Okay, so now the catch is you have to “kill” the process in order for the process to be re-ran and pick up the correct statistics. This isn’t the easiest process in the world to do, because if you try to just cancel the job through the process scheduler it may or may not cancel nicely. However, if you “kill” the SQL at the database level that is hung the application engine for go to ERROR, and you can restart the AE from the last committed point that it executed to.  This is much cleaner than any other method I have found, especially if you have a huge amount of stuff already done by the application engine prior to running into this issue.  Upgrades are notorious for this, which is why they have “update statististics” steps all throughout the upgrade process now.

So in Oracle SQL we need to find the SQL that is causing the issue:

select * from v$session where program like ‘%PSAESRV%’;  –See SQL running by Application Engine

select * from v$sql where sql_id = ‘SQL_ID from v$Session’;  — Confirm you have the write SQL

alter system kill session ‘{sid},{serial#}’;

It may take a few minutes to find the SQL causing the problems, but don’t worry, it will still be running after you find it.  Once it is killed, the AE will go to error, and you can restart it.  It will start up at the last commit point, which you can usually see if you query the AE Control table:

select * from ps_aeruncontrol;

Rerun your statistics for the schema or for the tables in question.

exec dbms_stats.gather_schema_stats(‘SYSADM’, cascade=>true);

Good luck out there!

Linux Curl with Proxy

What is Curl?  curl is a tool to transfer data from or to a server, using one of the supported protocols (DICT, FILE, FTP, FTPS, GOPHER, HTTP, HTTPS, IMAP, IMAPS, LDAP, LDAPS, POP3, POP3S, RTMP, RTSP, SCP, SFTP, SMB, SMBS, SMTP, SMTPS, TELNET and TFTP). The command is designed to work without user interaction.

So in the cloud you may find yourself on a server that needs to communicate to an external system but it isn’t allowed to go there directly.  In which case you will most likely have a proxy server to communicate through.  In PeopleSoft you can define a web gateway proxy server, however, to test to make sure that the server is working correctly with the proxy you will want to do a curl test.

Linux Command Prompt –> curl http://whatsmyip.com

This should return the external IP address of the system you are coming out of.  If this returns nothing you are likely blocked from going to that address.  So to push the request through the proxy server, you need to set the proxy in an environment variable:

Linux Command Prompt –> export http_proxy=http://myproxy.atmyserver.com:80

Linux Command Prompt –> curl http://whatsmyip.com

Now as long as the proxy is allowed to communicate to that address you should see the IP address of the system you are communicating from.

Oracle Sequences

Sometimes in the middle of the night you will be working on a conversion effort and you will need to populate a field with a sequence of values.  This is easily done using Excel but if you are dynamically loading data you may want to have a more flexible feature.  Oracle sequences have that ability.

Create A Sequence:

CREATE SEQUENCE adjustment_fix START WITH 1001 INCREMENT BY 1 NOCACHE NOCYCLE;

So this sequence will start are 1001 and each additional next value will by greater by 1 — so 1002, 1003, 1004….etc.  I had an oddball situation where I needed an 18 digit number that increased by 10000000. So in my case it was:

CREATE SEQUENCE adjustment_fix START WITH 300000000010000000 INCREMENT BY 10000000 NOCACHE NOCYCLE;

Now that you have the sequence, you need to populate the rows with the sequence.  So I created a temporary table and loaded all the values in the table.  Then I had another field for the sequence number.  I tried to insert the sequence while doing the insert into the staging table of the values I needed staged for the adjustment but it whined and complained.  So I did the insert and then did an update of the rows:

SQL:    UPDATE STAGING_TABLE_4_ADJ SET ADJ_SEQ = adjustment_fix.nextval;

BAM!  Now I have 2200 rows of adjustment data with a unique sequence number on each row so that I can load the staging data tables.