Exciting times!  New laptop is coming and I get to install the OS for a change.  So I had to figure out how to make the old USB stick bootable so that I can install the OS.

1. Insert a USB flash drive into a running computer.
2. Open a Command Prompt window as an administrator.
3. Type diskpart.
4. In the new command line window that opens, to determine the USB flash drive number or drive letter, at the command prompt, type list disk, Note the drive number or drive letter of the USB flash drive.
5. At the command prompt, type select disk #, where # is the drive number or drive letter of the USB flash drive.
6. Type clean. This command deletes all data from the USB flash drive.
7. To create a new primary partition on the USB flash drive, type create part pri
8. To select the partition that you just created, type select part 1
9. To format the partition, type format fs=ntfs quick  (if you need fat-32 format enter: fs=fat32 )
10. Type active
11. Type exit
12. When you finish preparing your custom image, save it to the root of the USB flash drive.

For some reason I was looking at my wordpress site and found that my database collation was set incorrectly.  I have about 10 other word press sites and all of them are correct, but I had one stand out.  I found that I had about 15 tables in my database with the incorrect collation as well. So to fix this, I did the following:

Database Level:
ALTER DATABASE CHARACTER SET utf8 COLLATE utf8_general_ci

Table Level (including the existing columns):
alter table convert to character set utf8 collate utf8_general_ci;

As most of you know, we maintain and patch many systems.  As such we have to constantly keep upgrading our systems to be able to upgrade our clients.  We tend to setup servers that can be used for patching and maintenance to multiple systems at the same time.  For example the latest PeopleTools release just came out and we were patching a Microsoft SQL Server setup and then switched over to patching an Oracle based system.

When using the PUM you must have the Oracle Clients installed, and so I typically say to customers that are on Microsoft SQL Server to install the PeopleTools with both the MSS & Oracle license codes as each install will give you different code.  I typically install them in separate directories so that unique files like “rdms.sqc.”  However sometimes I cut corners in the interest of saving time and space and I ran into an issue with Change Assistant the other day.

When running SQR from change assistant I was getting failures that didn’t appear to make any sense.  I went to the %PS_HOME%\bin\sqr\ora\binw and ran sqrw.exe individually and got a missing dll error:  bclw64.dll not found.  As it turned out I had installed the MSS 8.54 toolset to my PS_HOME, and then installed the 8.54.09 minor MSS & ORA files to my PS_HOME.  Thus I was missing several ORA unique system files.  After a couple re-installs I have 8.54 base ORA&MSS and 8.54.09 ORA&MSS filesets in my PS_HOME and SQR now runs from the ORA & MSS folders without an issue.  (Yes, I manually adjust the rdms.sqc and other files as necessary).

Happy Patching…..

Okay, in PeopleTools 8.53 you can now specify a digital certificate for SSH, not sure if this was there in 8.52, but in the past I would put the SSH keys (private and public) out in the sshkeys folders of the application server and reference them in the URL properties for SFTP configurations.

Well, you can now store these in the digital certificates area and just reference it using the alias key.  In order to make this work you need to generate an ssh key pair.  On a Redhat system you should be able to run:

ssh-keygen

Just follow the prompts.  You will need to have the openssl rpm’s installed.  This should generate and id_rsa and id_rsa.pub file in the users home directory under the .ssh directory.  You will want to append the id_rsa.pub key to the authorized_keys file (create it if it does not exist).

cat ~/.ssh/id_rsa.pub >> ~/.ssh/authorized_keys

chmod 644 ~/.ssh/authorized_keys

You should be able to check you key by issuing the following:

openssl rsa -in ~/.ssh/id_rsa -check

Now here comes the trick, if you try to copy the public and private key from the SSH window on a windows machine and paste it into the digital store, I found that this would cause the certificate to fail.  I had to sftp the private and public key to my local machine and use my trusty UltraEdit to open the files in unix mode and copy and paste the contents into the digital certificates windows.  It appears that when copying in windows mode it uses the CR/LF versus if you have it in Linux mode it will just have the LF.  This appears to make a big difference.  No errors either way except it just won’t work with the windows format.

NOTE:  If you are copying the .ssh directory from one server to another (multiple application servers) make sure that the permissions on this folder are 700.  If you have a separate batch processing server you will want to ensure that it too has the .ssh folder and keys for the assignment user.

I was doing some work with file attachments in PeopleSoft over the past couple of weeks and I have to say PeopleSoft’s ability to handle attachments is flat out bad.  However, it does work.  The client I was working with found that storing documents in the database was starting to consume an extreme amount of space which made the database have issues.

In PeopleTools 8.54 there is a batch copy attachment feature that lets you move attachments from one URL to another.  So you can define a URL for sftp and another for the database record and copy attachments to/from those URLs. In 8.53 there is just an online page for this, however, if you are doing a copy of more than about 1000 documents you will want to switch to a batch mode.

I was also fascinated with the orphan cleanup process that looks at the referencing record and the storage record and clears out any non-referenced storage record documents.  PeopleSoft does this by looking for the FILE_ATTACH_SBR subrecord in the record definitions and generates a list of attached file names and then compares that to the records associated with FILE_ATTDET_SBR subrecord.  This process cleared out about 25,000 documents that were not referenced.

When doing the actual copy from the database to the sftp (which was local), I did it in chunks by identifying the correct reference record and specifying that in the file record array for the copyattachment function.  As I had one record that had almost 200,000 documents, which if you don’t specify the reference records it will look at all the records with the FILE_ATTACH_SBR subrecord which in the Finance 9.2 environment I am in was 108 records, however only 8 had references.

I also built an index for the record with the actual document which was unique on the fields:  ATTACHSYSFILENAME, VERSION, FILE_SEQ, which is different then the delivered index thus allowing the look up to be much more efficient.