Category Archives: Technology

DB2

Generate a GUID or UUID in DB2

10/30/2013 Mark Jacobsen Leave a comment

Want to generate the equivalent of a GUID or UUID in DB2? The closest you’ll get is with GENERATE_UNIQUE(), but beware that you need to do some conversion on it as the value is “BIT DATA” and not character data. To put the value into a typical CHAR or VARCHAR field (like you would with a java UUID), use this…

select TRIM(CHAR(HEX(GENERATE_UNIQUE()))) from sysibm.sysdummy1;

I should probably also mention that there is the IDENTITY and SEQUENCE, but the GENERATE_UNIQUE() function is the closest you’ll get to a GUID or UUID.

Please remember to subscribe to the newsletter or feed to stay up to date!

Disclaimer: Thoughts and opinions are my own, and do not reflect the views of any employer, family member, friend, or anyone else. Some links may be affiliate links, but I don't link to anything I don't use myself.

Linux

Change permissions on files of a specific type in linux

10/17/2013 Mark Jacobsen Leave a comment

Need to change all *.ksh files to be executable under a directory, but not having luck with a recursive chmod? The issue is you need to combine chmod with a find and xargs like so…

find /home/user -name '*.ksh' | xargs chmod 744

The first piece lists all the files under the path that match *.ksh and passes them to xargs and chmod. If you want to see an example without changing any permissions, just substitute ls -l like so…

find /home/user -name '*.ksh' | xargs ls -l

DB2

Find the column causing a DB2 import to fail

10/16/2013 Mark Jacobsen Leave a comment

Ever run DB2 IMPORT and get a super helpful error like…

SQL0407N  Assignment of a NULL value to a NOT NULL column "TBSPACEID=7,
TABLEID=265, COLNO=2" is not allowed.  SQLSTATE=23502

SQL3185W  The previous error occurred while processing data from row "126" of 
the input file.

After you calm down from wanting to smack a DB2 developer in the face, remember the syscat tables and run this SQL to find out the table and column giving you grief…

select  * 
from    syscat.columns c
            inner join syscat.tables t
                on  c.TABSCHEMA = t.TABSCHEMA
                    and c.TABNAME = t.TABNAME
where   c.COLNO = 2 
        and t.TABLEID = 265
        and t.TBSPACEID = 7

Linux

How do I send email from the linux command line?

10/15/2013 Mark Jacobsen Leave a comment

Need to email from your linux command line? Assuming the underlying pieces are configured correctly (run the 1st example to see) here are the basics…

Send a test email

mail -s "Hi there" someone@somewhere.com

Include some body text…

echo "This is some body text" | mail -s "Hi there" someone@somewhere.com

Email the contents of a file to someone…

mail -s "My Subject" someone@somewhere.com < /path/to/your/file.txt

You can find more here

DB2

How can I be getting a deadlock trying to create a stored proc?

09/23/2013 Mark Jacobsen Leave a comment

If you’re getting an error similar to the following…

SQL0911N The current transaction has been rolled back because of a deadlock 
or timeout. Reason code "68". LINE NUMBER=171. SQLSTATE=40001

… and you know your tables are all just fine, it may be that someone (likely a DBA) has the SYSCAT.ROUTINES table locked up. Try running this…

SELECT RTRIM(R.ROUTINESCHEMA),RTRIM(R.SPECIFICNAME),RTRIM(R.ROUTINENAME) 
FROM SYSCAT.ROUTINES R 
WHERE R.ROUTINETYPE='P'  
ORDER BY RTRIM(R.ROUTINESCHEMA), RTRIM(R.SPECIFICNAME), RTRIM(R.ROUTINENAME)

…and if that errors, try running “WITH UR”. Assuming the 1st fails and the 2nd does not, have your neighborhood DBA kill any connections that may be causing the lock.

Linux

What timezone is my linux server configured for?

09/19/2013 Mark Jacobsen Leave a comment

To find out, just run the following command from the shell…

date +%Z

Linux

How do I run a script in the background?

09/17/2013 Mark Jacobsen Leave a comment

Use nohup! Here’s an example…

nohup /some/script.ksh

And even better, to redirect all output to a specific location…

nohup /home/user/script.ksh > /home/user/script.log 2>&1 &

Yes, the ” 2>&1 &” at the end is important.

DB2

DB2 Incremental Delete

07/15/2013 Mark Jacobsen Leave a comment

Ever have to delete a lot of records from a table in DB2 and can’t truncate, but you keep filling the transaction log? One way of solving the problem without having to manually update ranges of values in a where clause is to do an incremental delete in a stored proc. Here’s an example/template to get you started…


WHILE EXISTS (  SELECT  ITEM_ID 
		FROM    XX.TABLE
		WHERE   SOMETHING_NB = 1
		FETCH FIRST 1 ROWS ONLY )
DO
	DELETE FROM 
		(SELECT ROW_NUMBER() OVER() AS ROW_NB
		FROM    XX.TABLE
		WHERE   SOMETHING_NB = 1
		FETCH FIRST 20000 ROWS ONLY)
	WHERE ROW_NB <= 20000;
	
	IF ((L_SQLCODE_NB <> 0) AND (L_SQLCODE_NB <> 100)) THEN
		SIGNAL SQLSTATE '20003' SET MESSAGE_TEXT = 'Error deleting records';
	END IF; 
	
	COMMIT;
END WHILE;

Java

Resolving Ant javac “class file has wrong version” issues

07/03/2013 Mark Jacobsen Leave a comment

Ever run an ANT build in eclipse and it works just fine, but as soon as you run it outside of eclipse you get something like…

class file has wrong version 51.0, should be 49.0

The issue is likely that you need to “fork” in your javac task…

Before (with error)

javac srcdir="${build.path}" 
   verbose="true" 
   includeantruntime="false" 
   includejavaruntime="false"

After (no error)

javac srcdir="${build.path}" 
   verbose="true" 
   includeantruntime="false" 
   includejavaruntime="false" 
   fork="yes"

Hopefully this can save you about 2 hours some day!

Java

New cffreedom-utils repo on github

05/07/2013 Mark Jacobsen Leave a comment

For the last 10 years or so I’ve built up a number of Java utility functions and classes to help me write code easier. Over time I’ve probably had to re-write many of the functions as I’ve moved teams, lost the code, or just wanted to make it better. I’ve also had to jar up and help other developers use the code one too many times. Since I’m finally sick of that I’ve switch over to Maven for dependency and build management, moved the library to github, and am open sourcing the development so that others can take advantage of my work, and contribute to it to make it better.

You can find the project at https://github.com/communicationfreedom/cffreedom-utils. Please take a look, and consider helping make it even better.

Note: Since I’ve been using the project more and more the pace of change is pretty steep and the package structure has been changing frequently, but I believe that is starting to settle down a bit.