techlog

In this section, you will will find postings, code snippets (Java/Unix/Database), and recommendations from a broad perspective.  I have been a Developer and Database Administrator since 1994, mainly on the Unix side; therefore, everything listed will be from a Linux, Mac OS X, and Solaris slant. Database wise, my main experience is with Sybase on Unix platforms, although I do support Microsoft SQL Server and Oracle (Unix). Development wise, it's simple: Java on Unix...

Google Safari Violations

If you are a Mac user of Safari and happen to have a google account, you may want to check your preferences and site info/cookies - google is doing some very underhanded things when considering that you check your personal or business account under gmail or google+ and all of sudden, there's "cross-selling" going on under you nose. You may want to turn on stealth browsing in Safari or disable cookies and etc. Actually the EU and others are onto them and fines are being claimed at this moment. 

Audacity Click Removal

For those of you who need to clean up clips from overloaded recordings from video or audio podcasts or just plain audio recordings of anything. It can be done with some effort using the open source audio software called Audacity - available from Audacity: Download  (sourceforge - all platforms).

All you need to do is import the audio file, choose "Show Clipping" from the view menu, then zoom in on the "red" parts, hi-lite the bad audio and choose the "Click Removal" item from the "Effect" menu. Then experiment with the settings to se which one removed the clipping or pops without changing the pitch too much - that is the only danger you need to be concerned with as you do not want to alter it too much. If that does not work, zoom in to the waveforms, choose "Amplify" from the Effect menu, lower it to -1.8 db, then choose a section of the waveform around 10ms or so and choose "Repair" from the Effect menu - that will fix any messed up audio guaranteed, but it is a time consuming process. It's totally worth it though...

Beware Fedora 14 install media

For those in the mood to update their Linux Desktops who are on the Fedora platform, do yourself a favor, stay where you are or stop at Fedora 13. Even better, if you really want to upgrade, use `yum update yum` then `yum <distro and version tags from Fedora>`. Basically, the bootloader from either USB media, CD's or DVD's will crash if your BIOS/machine is older than 2006 or it will freeze on PCI-Bridge setups no matter how well you tuned your IRQ's per PCI/AGP device. It's just not friendly, even in basic video driver mode. Newer intel machines - 2008 and better will have no problems, except for the clutch-of-death-PCI-bridge. I can also attest that clonezilla works like a charm using partclone (ext4) when things really go awry, so I suggest doing a clonezilla clone before you plan to upgrade - the recovery is very fast and thorough. However, if your BIOS is a mess, you will get stack traces on startup and possibly in the middle of a recovery if a timeout (read block) happens that is greater than 61 sec.

MySQL Replication

For any DBA with a long history of Sybase, Oracle or SQL Server, MySQL really isn't anything new or complicated, but with ver. 5 and above, there's a lot of interesting tuning involved and places where you can fall on your face. One area is replication. It's quite different and not as mature as Sybase, Oracle or MS SQL Server, but pretty cool and easy in a lot of ways. Once again, lots of gotchas though. 

First and foremost - prepare. Chances are you are being dragged in for setting up a replicant of an existing master DB. Be sure to get the user, password, full hostname (or IP), port, relay log name and location and position (via `show master status;` command). This data will end up in your master.info file in the <mysql> home directory that is running (assuming you have mysql installed). The "position" data is only useful if there is no activity and transactions have been flushed. Enter this data either by using the `change master to....` command with all parameters listed or edit a file in the home of your mysql database called master.info.

Dynamic matrix/crosstab using SQL

There's a less costly way of creating a summary on a web page or displayed through another data bound tool for what could be deemed a 'crosstab'. This kind of functionality is built into tools such as Business Objects or Crystal Reports (old name), but what if you wanted to do it in a stored procedure, without using expensive cursors and display it back using simple Servlets/JSP/JSF. I've used this method for quite some time. It involves using `execute(<sql command>)` within a stored proc that you build based on selects that you would normally reserve for a cursor. Here's some pseudo-code with explanations:

create proc summary as

declare @column1 varchar(30)

select column1, processed=0 into #results from main_table where ......

while exists(select 1 from #results where processed = 0)

        begin

        set rowcount 1

       select @column1 = column1 from #results where processed = 0

       set rowcount 0



Stable Supported ASE

There were recent announcements from Sybase (www.sybase.com) you may have gotten in your inboxes about EOL software. Most of us DBA's really just focus on ASE, IQ or Repserver. That's pretty much what myself and colleagues of mine did in the past weeks, but it brought some things to light about truly stable versions and EBF's . Here's my take on this by version and OS (note: if you are using a Sybase database on Windows, you have bigger problems, and you should know it)

1) If you MUST stay on ASE 12.x.x, then the only stable version you can use on Solaris RISC (or x86) is 12.5.4 - latest ESD or EBF, this version is 64 Bit only, phasing out 12.5.2 or 3 versions that were 32 Bit mainly - these "back-fills" are mainly mandated by vendor apps who are slow to certify on platforms. I strongly suggest pushing for this if you have to stay on 12. I have years of stable experience on this version on SPARC, using 32 engines and over and 64 GB or cache and over. It's hard to beat on reliability. (side note: EMC raw devices done right)

The joys of Sybase ASE SYSAM-2_0

For all of you using Sybase Adaptive Server Enterprise at your sites, beware the Macrovision licensing monster SYSAM 2.0. SYSAM 1.0 was fine with all of us because it didn't really work so you could get away with some upgrade work for platforms. SYSAM 2.0 actually works on ASE 15.x, so you have to be stringent with hostid's, hostname's and the actual number of cores you are licensed for if you are using the LT=CP model (CPU based license). However, be aware that the SYSAM template files in Linux are buggy, and you will need to correct the template file first before you try to upgrade your license. For instance, there will be a line for LT=SR, which in "old" Sybase terms, meant a perpetual server license. That doesn't float, so you will need to change it to LT=CP where applicable. 

As for Solaris x86 and Linux alike, keep in mind that the SYSAM-2_0/bin/lmutil utility is still buggy as ever. If you do a `./lmutil lmreread` to actually reread the license directory, you will get an error message for sure, but it will work. It will scan and actually add up the license files you have there for applicable products you own or use. There are times where you will re-allocate your licenses and it might scare you to think that it did not take, but, automagically, it will. Finally, tech support has a limited number of engineers that can help with this, but the dudes in Hong Kong were a great help - kudos to them, and kudos to Sybase for making a complex license model work from the server-side model.

Java to Active Directory

JNDI is for real and it works, but the key to remember when writing a Java or JSP/Servlet page to get to Microsoft Active Directory is........ use the secure LDAP server to the Active Directory setup by the Wintel group in your company. I'll outline the code you will need but pay close attention to the port. You'll see a lot of stuff on the web that tells you can can go http, but you can't change any values unless you have SSL certs between your webserver and the AD server, and you MUST use the SSL, secure connection to the LDAP AD server to make anything work right. Let's start with the includes (JSP version):

<%@ page import="java.util.Hashtable" %> 
<%@ page import="javax.naming.*" %> 
<%@ page import="javax.naming.ldap.*" %> 
<%@ page import="javax.naming.directory.*" %> 
<%@ page import="java.io.*" %> 
<%@ page import="sun.misc.BASE64Encoder" %> 
<%@ page import="sun.misc.BASE64Decoder" %> 
<%@ page import="java.io.BufferedInputStream" %> 
<%@ page import="java.io.BufferedReader" %> 
<%@ page import="java.io.IOException" %> 
<%@ page import="java.io.InputStream" %> 
<%@ page import="java.io.InputStreamReader" %>

The next step outlines your connection and how you will be issuing commands to the AD:

String  Domain = "@YOURDOMAIN";
        String  UserLogin = request.getParameter("loginID")+Domain;
        String  WinLoginID = "CN="+request.getParameter("loginID")+",OU=DOMAIN,OU=DOMAIN info,OU=Users,OU=MORE INFO,OU=EVEN MORE INFO,DC=Name";
// --### Log on to the LDAP server as Self

                                Hashtable env = new Hashtable();
                                System.setProperty("javax.net.ssl.trustStore", keystore);
                                env.put(Context.INITIAL_CONTEXT_FACTORY,
                                        "com.sun.jndi.ldap.LdapCtxFactory");
                                env.put(Context.PROVIDER_URL, "ldap://YOUR_SERVER:636"); //389
                                env.put(Context.SECURITY_PROTOCOL, "ssl");

                                env.put(Context.SECURITY_AUTHENTICATION, "simple");
                                env.put(Context.SECURITY_PRINCIPAL, UserLogin);
                                env.put(Context.SECURITY_CREDENTIALS, OldPswd);


                                try {

                                DirContext ctx = new InitialDirContext(env);

                                // --## change password is a single ldap modify operation
                                // --## that deletes the old password and adds the new password

                                ModificationItem[] mods = new ModificationItem[2];

                                // --## Firstly delete the "unicdodePwd" attribute, using the old password
                                // --## Then add the new password,Passwords must be both Unicode 
                                // --## and a quoted string
                                String oldQuotedPassword = "\"" + OldPswd + "\"";
                                byte[] oldUnicodePassword = oldQuotedPassword.getBytes("UTF-16LE");
                                String newQuotedPassword = "\"" + NewPswd + "\"";
                                byte[] newUnicodePassword = newQuotedPassword.getBytes("UTF-16LE");


                                mods[0] = new ModificationItem(DirContext.REMOVE_ATTRIBUTE, new BasicAttribute("unicodePwd", oldUnicodePassword));
                                mods[1] = new ModificationItem(DirContext.ADD_ATTRIBUTE, new BasicAttribute("unicodePwd", newUnicodePassword));

                                // --## Perform the update

                                ctx.modifyAttributes(WinLoginID, mods);

                                ctx.close();

The example code you see above is for changing your own password logged on as yourself (a real user), but you can issue commands for the ctx object in any way you like. This code works in a very locked down environment, so you can be sure it will work with yours if properly setup by your Wintel Admins. Remember to get the certs from your AD server and import them to the WEBSERVER you are using - it will normally be in the environment that is set for your JRE - find that physical directory and place the certs in the cacerts directory by doing a proper import.

Btrieve woes

If you ever need to convert an existing, horrific database from Btrieve to something good like Sybase or Oracle. You will first need to find the Btrieve client and ODBC drivers from either the server it's running on (for clunker novell crap, it would be in the SYS directory). Pervasive is the ONLY set of drivers that really work, so go to www.pervasive.com to get them or sue what you find in the directory. It's essentially 32-Bit on top of 16-Bit poop. Do the simple install and have your IP and port ready for the Btrieve database server. 

Once you have the connection working and can see a system DSN for it. Open up MS Access (I know, I hate it too but it's a shortcut), then do a link table from the menu and choose the ODBC entry you just made. Link all of your tables at this point. Next, you will make a macro entry for each table, choosing the "Export Database" action and make an ODBC entry for you destination database (Sybase, Orcale & etc.) and formulate the ODBC string to enter into the ODBC string field. Then, make sure "Structure Only" is set to "No", then cross your fingers and run the macro! 

Buh-Bye Flash

Abandoned flash as a web technology. It's too resource hungry and buggy on Unix based platforms, so I changed to a simple web design and will use html5 elements from now on for media content. 


Contact: daquijne@josephdaqui.com (copyright 2010)