This is the scenario, client needs to move from database stored file attachments to an actual file share, this allows them to remove 1/4 of their entire database storage needs out to a storage area.  Clearly this is a good thing. So, here is the quick run down on what we did:

1. Copy all the existing URL references that contained record:// for backup purposes.

2. Setup a SSH account with a SSH key pair for security

3. Install the SSH key pair into the digital certificates

4. Change all the existing URLs that point to the database storage to the new SFTP storage locations.

5. Run the Orphan cleanup process for File Attachments

6. Copy the file attachments from the OLDURL to the NEWURL

7. Setup the default file attachment server to use the new URL attachment server.

8. Generate a list of all the attachments moved into the file storage and compare that against what was actually in the attachment record

9. Purged the attachment record of all the migrated files.

10.  Updated attachment reference records when necessary if they referenced the old URL in any way.

This is where we ran into an interesting problem with unicode characters.  The process was relatively slow, and we found that to move approximately 200,000 files took about 40 hours.  So to get into production what we did was made a copy of production and run the process in a test environment.  Then we moved all the files that we copied to the production file share and than removed all the entries from the database attachment record that were already moved and then ran the tiny subset of new attachments out to the storage so we could minimize our downtime.

Smart Thinking Newsletter

5 Ways an Effective ERP Helps to Grow Your Business

In today’s competitive market, it’s more important than ever that your company runs efficiently and has good data management in order to serve your customers quickly and effectively.

A great ERP strategy helps you save on costs, leaving more room in your operating budget for marketing. Meanwhile, your company will be able to react more quickly and plan ahead.

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…..