Category Archives: Technology

Find the column causing a DB2 import to fail

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

How do I send email from the linux command line?

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

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.

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

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.

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.

How do I run a script in the background?

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.

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.

DB2 Incremental Delete

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

Resolving Ant javac “class file has wrong version” issues

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!

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.

New cffreedom-utils repo on github

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.

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.

XPath Query Not Returning any Results in Java

In looking over documentation on XPath queries it seemed easy enough to be able to pull out values from XML, but try as I might I kept getting no results back. Even searching on the root element “/elementName” didn’t return any results. Of course, as with all things Java there’s a trick and that trick is to call setNamespaceAware(). Here’s a quick and dirty example…


DocumentBuilderFactory domFactory = DocumentBuilderFactory.newInstance();
domFactory.setNamespaceAware(false); // NEVER FORGET THIS
DocumentBuilder builder = domFactory.newDocumentBuilder();
XPathFactory factory = XPathFactory.newInstance();
XPath xpath = factory.newXPath();
XPathExpression expr = xpath.compile("/items/item[@id='2']");
return (NodeList)expr.evaluate(domDocument, XPathConstants.NODESET);
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.

Output a DOM Document as XML using Java

Ok, here’s another nerdy post for all the java developers out there.  Hopefully it saves you some time.

Let’s say you have a XML DOM Document that you’ve been modifying in your code, and now want to write it back out as XML.  One would think there would be a nice toXml() method or something on the Document, but there isn’t.  Instead, make sure you have the xalan.jar file on your class path and use this function…


public static String getDomDocumentAsXml(org.w3c.dom.Document domDocument)
{
	try
	{
		TransformerFactory tf = TransformerFactory.newInstance();
		Transformer transformer = tf.newTransformer();
		transformer.setOutputProperty(OutputKeys.INDENT, "yes");
		StreamResult xmlOutput = new StreamResult(new StringWriter());
		transformer.transform(new DOMSource(domDocument), xmlOutput);
		return xmlOutput.getWriter().toString();
	}
	catch (Exception e) { e.printStackTrace(); return null; }
}
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.