Weekly Round-Up: 12/22/13 to 12/28/13

The Weekly Round-Up is a once weekly collection of my #1Aday daily shares. Hope you find something of interest!

Mon 12/23
The Minimalist Mom @racheljonat reminds us: You Can Stop Shopping Now – http://mjg2.net/l/1901

Tue 12/24
Adam Penenberg @Penenberg challenged hackers to investigate him and what they found out is chilling – http://mjg2.net/l/1896

Thu 12/26
Sean Blanda @SeanBlanda gives 10 Tips for an Awesome Coffee Meeting – http://mjg2.net/l/1897

Fri 12/27
Jegede Abayomi @abayomi_jegede tells us about 10 Habits of The Richest People in the World – http://mjg2.net/l/1898

Weekly Round-Up: 12/15/13 to 12/21/13

The Weekly Round-Up is a once weekly collection of my #1Aday daily shares. Hope you find something of interest!

Mon 12/16
Amy Morin @AmyMorinLCSW on 8 Scientific Secrets Of A Happy Marriage – http://mjg2.net/l/1890

Tue 12/17
Peter Shankman @petershankman On Entitlement, Regardless Of Your Generation <- this. So much this. - http://mjg2.net/l/1892

Wed 12/18
Why 9 to 5 is a Joke (And How to Deal) – http://mjg2.net/l/1893

Thu 12/19
MMM @MrMoneyMustache tells you how to Get Rich With The Position of Strength – http://mjg2.net/l/1894

Fri 12/20
How to Plant Ideas in Someone’s Mind – http://mjg2.net/l/1895

Weekly Round-Up: 12/8/13 to 12/14/13

The Weekly Round-Up is a once weekly collection of my #1Aday daily shares. Hope you find something of interest!

Mon 12/9
Mark Panay @redeye on 13 Productivity Tips from People Getting Stuff Done – http://mjg2.net/l/1887

Tue 12/10
MMM @MrMoneyMustache says we’re Predictably Irrational – http://mjg2.net/l/1888

Thu 12/12
As a father of girls this makes me sad: How the Media Failed Women in 2013 – http://mjg2.net/l/1900

Fri 12/13
jlcollinsnh: Case Study #4: Using the 4% rule and asset allocations – http://mjg2.net/l/1889

DB2: How to find records with non-printable characters (SQLSTATE 01517)

If you run a select statement and get the following error…

"SQLSTATE 01517: A character that could not be converted was 
replaced with a substitute character."

… you can use the TRANSLATE function to strip away printable chars, and compare that to a zero length string like so…

WHERE TRANSLATE(UPPER(FIELD_X),'','ABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890!@#$%^&*()-=+/\{}[];:.,<>? ') <> '' 

Weekly Round-Up: 12/1/13 to 12/7/13

The Weekly Round-Up is a once weekly collection of my #1Aday daily shares. Hope you find something of interest!

Mon 12/2
Joshua Fields Millburn @JFM on Five Steps Toward a More Meaningful Holiday Season – http://mjg2.net/l/1891

Tue 12/3
Brianna Wiest @briaeliza – 18 Things Everyone Should Start Making Time For Again – I agree with some of them. – http://mjg2.net/l/1883

Wed 12/4
Leo @zen_habis on The Necessary Art of Subtraction – http://mjg2.net/l/1884

Thu 12/5
Michelle Schroeder @senseofcents on 10 Things Successful People Do Differently To Reach Their Dreams – http://mjg2.net/l/1885

Fri 12/6
Kimberly Weisul @weisul on How I Became the Kind of Person Who Can Work a Room – http://mjg2.net/l/1886

How to concatenate multiple columns in a DB2 select statement to return a single column

Use “||” like so…

SELECT LastName||', '||FirstName 
FROM [schema].[table]

If one of the columns is numeric you’ll probably want to convert to CHAR and TRIM like so…

SELECT LastName||', '||FirstName||' - '||TRIM(CHAR(EmplId))
FROM [schema].[table]

Extract and insert tables (or portions of them) in DB2 – similar to BCP for Sybase and SQL Server

You’ll need to be in the DB2 Command Line for this. Also, the version of your DB2 client should match the version of the DB or be higher than the DB version (i.e. no using a version 7 client to go after a version 8 DB).

To get data out of a db2 table:

db2 "export to [file_name] of [file_type] select * from [table]"

[file_name] is a fully qualified name of the ouput file
[file_type] is either del (delimited) or ixf (db2 native format).
[table] is a fully qualified table, nickname, or view

For example:

db2 "export to /home/user/people.ixf of ixf select * from db2dba.people"

To import data into a db2 table:

db2 "import from [file_name] of [file_type] commitcount [value] insert into [table]"

For example:

db2 "import from /home/user/people.ixf of ixf commitcount 10000 insert into db2dba.people"

commitcount is optional but should be used to avoid filling up the transaction log. I generally use 10k to 20k.

Notice on the export command you use SQL syntax to get the data. You could use a where clause. You can also select and insert specific columns (Strongly recommended).

The import command allows you to insert into, replace into (replaces existing data), and create into (creates a new table).

You may wish to consult the DB2 Command Reference manual for more information

Should I use DB2 LOAD or IMPORT?

Use LOAD when:

  • Performance is critical. Load outperforms Import in all scenarios, sometimes by as much as a factor of 10 (May be closer to 6 times for UNIX systems).
  • You want to LOAD FROM CURSOR to avoid the creation of an intermediate file.

Use IMPORT when:

  • You have smaller tables.
  • You want additional “safety” in the case of an error.
  • Target table needs to be created from the input file (applicable to PC/IXF files only.)
  • Logging of every inserted record is necessary.
  • You require triggers to fire.
  • You need to be able to update the table while appending data to the table.

In general I would recommend IMPORT unless performance is absolutely critical or you have more than 10 million records (yes, that’s relatively arbitrary – the point is a LOT of data). The primary reason for this is that a botched load can send your entire table space into backup pending and leave you dead in the water. If you do decided to do a load, make sure you include steps to recover from the failed load (but that’s a topic for another day).

How do I override the JVM Heap Size?

If you’ve ever received a java.lang.OutOfMemoryError when processing a large amount of data, there’s a chance you need to manually specify the JVM size if you can. Here’s an example…

java -Xms64m -Xmx256m MyProgram

… Of course, another option you may want to consider is to structure your program more efficiently (i.e. don’t store all that data in memory, process in blocks, use a tool more suitable to the task like BCP or DB2 LOAD).

Want to read more on the subject? Read this

Weekly Round-Up: 11/24/13 to 11/30/13

The Weekly Round-Up is a once weekly collection of my #1Aday daily shares. Hope you find something of interest!

Wed 11/27
More good reasons to skip Black Friday: The Dirty Secret of ‘Discounts’—Black Friday and Beyond – WSJ.com – http://mjg2.net/l/1882