tag:blogger.com,1999:blog-87677095611128290052024-03-14T07:41:02.431+01:00154 pmtips and tricks around digital mattersBernd Kuennenhttp://www.blogger.com/profile/09810163923342765495noreply@blogger.comBlogger16125tag:blogger.com,1999:blog-8767709561112829005.post-14829963311092147722008-07-31T15:11:00.004+02:002008-07-31T15:30:09.966+02:00Tutorials for IBM DB2 certification<div style="text-align: justify;">Recently I was asked frequently about material regarding the IBM DB2 exams 700 and 701. My answer was always the same and I'll post it today so that next time I'll just have to send a link to this article. :-)<br /><br />If you're preparing for an IBM DB2 exam, have a look at the IBM web site and especially at some pages in the "IBM developerWorks". There're tutorials for exam 700 (DB2® (Version 8) Fundamentals Certification) with the title "<a href="http://www-128.ibm.com/developerworks/db2/library/tutorials/db2cert/db2cert_V8_tut.html">DB2 V8 Family Fundamentals certification prep tutorials</a>" and for Exam 701 (DB2 UDB V8.1 for LUW Database Administration) called <a href="http://www.ibm.com/developerworks/db2/library/tutorials/db2cert/701_prep.html">DB2 UDB V8 DBA Certification prep tutorials</a>. Just follow these links.</div><br /><a href="http://www.mister-wong.de/add_url/" onclick="'location.href=" action="addurl&bm_url=" bm_description="+encodeURIComponent(document.title)+" title="Diese Seite zu Mister Wong hinzufügen" target="_top"><img src="http://www.mister-wong.de/img/buttons/wongbig08.gif" alt="Diese Seite zu Mister Wong hinzufügen" border="0" /></a><script>digg_skin = 'compact'; </script><script src="http://digg.com/tools/diggthis.js" type="text/javascript"></script>Bernd Kuennenhttp://www.blogger.com/profile/09810163923342765495noreply@blogger.com0tag:blogger.com,1999:blog-8767709561112829005.post-55415807724592608282008-02-24T00:07:00.005+01:002008-02-24T01:10:53.992+01:00Make my day<div style="text-align: justify;">Sometimes you need things to be done on the beginning or the middle of the month. Fees need to be payed, invoices to be sent. A database could hold the next day when the task should be performed. But how to calculate it in SQL if the result needs to be the 1st or 16th?<br /><br />So lets start playing with days. This little code snippet results in the first day of the current month:<pre>select<br /> current_date + 1 day<br /> - ( day(current_date) ) days<br />from sysibm.sysdummy1;</pre>Subtract the number of the current day and add one. So in Feb. 2008 it would bring up the date expression '2008-02-01'. Now we need a code which gives us the delta of zero or 15 days, depening on the current day and if it's in the lower or the upper part of the month:<br /><pre>select<br /> ( (day(current_date) / 16 ) * 15 ) days<br />from sysibm.sysdummy1;</pre>Due to that day(current_date) is maximum 31, the integer division by 16 will result in zero or one. Multiplied with 15, this is the delta we need. So together these codes snippets will give us the first or 16th of the month:<pre>select<br /> current_date + 1 day<br /> - ( day(current_date) ) days<br /> + ( (day(current_date) / 16 ) * 15 ) days<br />from sysibm.sysdummy1 ;</pre>It can be varied for values of 16 and above, to provide e.g. the 20th or the 1st day of the current month. Remember to divide by x and then multiply by (x-1) in the last addend. Values of 15 and lower won't work.</div><br /><a href="http://www.mister-wong.de/add_url/" onclick="'location.href=" action="addurl&bm_url=" bm_description="+encodeURIComponent(document.title)+" title="Diese Seite zu Mister Wong hinzufügen" target="_top"><img src="http://www.mister-wong.de/img/buttons/wongbig08.gif" alt="Diese Seite zu Mister Wong hinzufügen" border="0" /></a><script>digg_skin = 'compact'; </script><script src="http://digg.com/tools/diggthis.js" type="text/javascript"></script>Bernd Kuennenhttp://www.blogger.com/profile/09810163923342765495noreply@blogger.com0tag:blogger.com,1999:blog-8767709561112829005.post-30270379561086168122008-02-16T00:17:00.009+01:002008-02-18T22:31:24.190+01:00Moving target<div style="text-align: justify;">Strolling around the internet, I came across an <a href="http://www-128.ibm.com/developerworks/db2/library/techarticle/dm-0403melnyk/index.html">article</a> of Roman Melnyk about moving data between DB2 databases. Usually you'd try to do a backup and restore, but sometimes you have to use the tools Roman described, db2move and maybe db2look, too.<br /><br />We're using those two command line programs since choosing DB2 (v7) as part of the platform for our web applications. They are very useful if you need to copy a whole database but don't have the time to take the source database offline for backup. Or if you just want to copy a part of a database.<br /><br />When changing the hardware platform for our DB2 servers from Intel to Power hardware we discovered another reason for using db2move and db2look: The x86 architecture is a little-endian one, placing the least significant byte of a multiple byte integer first, while the Power CPU is working big-endian. (See <a href="http://en.wikipedia.org/wiki/Endianness">http://en.wikipedia.org/wiki/Endianness</a> for details. I guess you could start a holy war about these two philosophies.) This inhibits the backup/restore strategy totally if you need to copy data between these two platforms.<br /><br />Although the article was published 2004 the topic won't get out of date and if you need to know how to transfer data between DB2 systems it's worth reading.<br /><br />P.S.: Of course big-endian is the right way to look at data. ;-)<br /></div><br /><a href="http://www.mister-wong.de/add_url/" onclick="'location.href=" action="addurl&bm_url=" bm_description="+encodeURIComponent(document.title)+" title="Diese Seite zu Mister Wong hinzufügen" target="_top"><img src="http://www.mister-wong.de/img/buttons/wongbig08.gif" alt="Diese Seite zu Mister Wong hinzufügen" border="0" /></a><script>digg_skin = 'compact'; </script><script src="http://digg.com/tools/diggthis.js" type="text/javascript"></script>Bernd Kuennenhttp://www.blogger.com/profile/09810163923342765495noreply@blogger.com0tag:blogger.com,1999:blog-8767709561112829005.post-65554072983568733742008-01-30T21:45:00.001+01:002008-02-18T22:31:54.291+01:00Generating daily usage statistics<div style="text-align: justify;">Sometimes you'll want to know how many logins there're on your web site and how the daily usage profile looks like. Usually the first attempt to evaluate this is to copy some log files or unload some database table which holds the login information and then import it to Excel or a similar spreadsheet generator.<br /><br />But in many cases it can be done by the database directly. Here's a DB2 example. Assuming there's a table "events.logins" holding the login events with a date and a time column, then this query would bring up a daily usage profile based on the average data of the last 7 days:<br /></div><br /><pre>select<br />tmptable.hr as Hour_of_day,<br />tmptable.cnt as Num_Logins,<br />substr(<br /> repeat('#', tmptable.cnt * 60 / (<br /><br /> select<br /> max( maxquery.cnt ) as max<br /> from (<br /><br /> select<br /> hour(time) as hr,<br /> count(hour(time)) as cnt<br /> from<br /> events.logins<br /> where<br /> date >= (current_date - 7 days)<br /> group by<br /> hour(time)<br /> ) maxquery<br /><br /> )<br />),1,60) as Logins_Profile<br />from (<br /><br />select<br /> hour(time) as hr,<br /> count(hour(time)) as cnt<br />from<br /> events.logins<br />where<br /> date >= (current_date - 7 days)<br />group by<br /> hour(time)<br /><br />) tmptable<br />order by<br />tmptable.hr<br />;</pre><br /><div style="text-align: justify;">This query includes two temporary tables. The upper one is used to evaluate the maximum logins per hour, which is necessary to normalize all counts. The second is generating the raw data, means the hour of the day and the number of logins. The result looks like this:<br /></div><br /><pre>HOUR_OF_DAY NUM_LOGINS LOGINS_PROFILE<br />----------- ----------- ------------------------------------------------------------<br /> 0 36 ##<br /> 1 43 ##<br /> 2 52 ###<br /> 3 55 ###<br /> 4 61 ####<br /> 5 73 ####<br /> 6 119 ########<br /> 7 294 ####################<br /> 8 625 ##########################################<br /> 9 846 #########################################################<br /> 10 880 ############################################################<br /> 11 812 #######################################################<br /> 12 663 #############################################<br /> 13 673 #############################################<br /> 14 685 ##############################################<br /> 15 662 #############################################<br /> 16 548 #####################################<br /> 17 359 ########################<br /> 18 190 #############<br /> 19 123 ########<br /> 20 112 #######<br /> 21 87 #####<br /> 22 75 #####<br /> 23 48 ###<br /><br />24 record(s) selected.<br /></pre><br /><a href="http://www.mister-wong.de/add_url/" onclick="'location.href=" action="addurl&bm_url=" bm_description="+encodeURIComponent(document.title)+" title="Diese Seite zu Mister Wong hinzufügen" target="_top"><img src="http://www.mister-wong.de/img/buttons/wongbig08.gif" alt="Diese Seite zu Mister Wong hinzufügen" border="0" /></a><script>digg_skin = 'compact'; </script><script src="http://digg.com/tools/diggthis.js" type="text/javascript"></script>Bernd Kuennenhttp://www.blogger.com/profile/09810163923342765495noreply@blogger.com0tag:blogger.com,1999:blog-8767709561112829005.post-6595068060197115582008-01-11T22:18:00.002+01:002008-02-18T22:32:15.273+01:00Spam vs. CO2<div style="text-align: justify;">Some are discussing CO<sub>2</sub>-free email services, means they'd like to power their servers with electricity from regenerative sources. The goal is to bring this to market as a product called "green email" or similar. But can an email be CO<sub>2</sub>-free at all? It's not only the mail server of the sender but also that of the recipient plus dozens of relays, routers, switches and other systems in between. They all would have to be powered by regenerative sources - a very ambitious aim.<br /><br />But there's another aim in this. Reducing greenhouse gas emissions by changes in the email system is possible - by reducing spam. Latest articles are saying that about 90% of all emails sent world wide are unwanted, mostly spam. So here lies a big potential of saving resources. Fight spam and save the world a little bit.<br /></div><br /><a href="http://www.mister-wong.de/add_url/" onclick="'location.href=" action="addurl&bm_url=" bm_description="+encodeURIComponent(document.title)+" title="Diese Seite zu Mister Wong hinzufügen" target="_top"><img src="http://www.mister-wong.de/img/buttons/wongbig08.gif" alt="Diese Seite zu Mister Wong hinzufügen" border="0" /></a><script>digg_skin = 'compact'; </script><script src="http://digg.com/tools/diggthis.js" type="text/javascript"></script>Bernd Kuennenhttp://www.blogger.com/profile/09810163923342765495noreply@blogger.com0tag:blogger.com,1999:blog-8767709561112829005.post-8859936209253569322008-01-03T19:38:00.002+01:002008-02-18T22:32:36.419+01:00Green IT<div style="text-align: justify;"><a onblur="try {parent.deselectBloggerImageGracefully();} catch(e) {}" href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhls4JkVtOpvLS_UJTxCC-9vWlFIloGt50Fb0_t3Sap517o_w7UCTJkOtothpmFB43tN-0zUahBsnYEhYv6holbrPpfVz4N0KHgN0wJ9yobvpym8jnjX12qCDum-OG1qleF16SIa_mVUoU/s1600-h/green-it-dollar.jpg"><img style="margin: 0pt 0pt 10px 10px; float: right; cursor: pointer; width: 200px; height: 203px;" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhls4JkVtOpvLS_UJTxCC-9vWlFIloGt50Fb0_t3Sap517o_w7UCTJkOtothpmFB43tN-0zUahBsnYEhYv6holbrPpfVz4N0KHgN0wJ9yobvpym8jnjX12qCDum-OG1qleF16SIa_mVUoU/s320/green-it-dollar.jpg" alt="" id="BLOGGER_PHOTO_ID_5151618233780470418" border="0" /></a>... is one of the current buzz words in IT business. Every hardware vendor is offering some special to help his customers reducing their CO2 emissions and thus saving the planet. Where the saying originally was "sex sells", in the digital world it became "green IT sells".<br /><br />Even in view of so much heroism the fact must be taken in doubt that the world is full of modern, environmental Clark Kents. Although in a <a href="http://www.computerwoche.de/index.cfm?pid=436&pk=1370">poll</a> of the german IT magazine <a href="http://www.computerwoche.de">Computerwoche</a> 42% said their motivation was to protect the environment, in my opinion this is just a desirable side effect. At least 32% probably told the truth and said the reason for their engagement in green IT was to reduce the costs of operation (electricity for operation and cooling etc.).<br /><br />So in the end one could modify another saying: Money makes the world go round - and helps saving it.<br /></div><br /><a href="http://www.mister-wong.de/add_url/" onclick="'location.href=" action="addurl&bm_url=" bm_description="+encodeURIComponent(document.title)+" title="Diese Seite zu Mister Wong hinzufügen" target="_top"><img src="http://www.mister-wong.de/img/buttons/wongbig08.gif" alt="Diese Seite zu Mister Wong hinzufügen" border="0" /></a><script>digg_skin = 'compact'; </script><script src="http://digg.com/tools/diggthis.js" type="text/javascript"></script>Bernd Kuennenhttp://www.blogger.com/profile/09810163923342765495noreply@blogger.com0tag:blogger.com,1999:blog-8767709561112829005.post-73819539332059037382008-01-01T21:44:00.002+01:002008-02-18T22:33:30.721+01:00Final shutdown<div style="text-align: justify;">Recently we had to abandon an external Linux system which was operated by a hosting provider. Although just about five years old and still fine for our requirements, the provider wanted to take the hardware out of service. Of course we wanted to make sure that the system, once given back, was clean and none of our data could be exposed to third parties.<br /><br />Our team named this project the "<a href="http://de.wikipedia.org/wiki/Operation_Regenbogen_%28U-Boote%29">Operation_Regenbogen</a>" (operation rainbow) which originally in the last days of WWII was an order to all german U-boats to sink their vessels. In some ways our externally hosted Linux system was an U-boat. For years it has been doing its service far away from home, silently, realiable, with communications encrypted. And now it had to be made sure that its secrets are kept. But how to do this properly on a server you don't have a direct attached console to?<br /><br />First, we unmounted all the unnecessary partitions and cleared them with "dd if=/dev/zero of=/dev/hdaX bs=4M". After switching off swap and clearing the swap partition the same way, all unneeded daemons were stopped and all TCP ports closed - except ssh of course. Then came the difficult part: Would a dd from zero device to hda device work?<br /><br />We decided for the safe way. After cleaning up the remaining filesystems / and /boot, especially the /var subfolders, we first cleared the free space by filling a dummy file with zeros: dd if=/dev/zero of=/dummyfile bs=1M ; rm /dummyfile . Then we gave the order to sink the boat with a "dd if=/dev/zero of=/dev/hda bs=4M".<br /><br />While pinging the server, we waited for things to happen. Then some time later, actually the prompt came back. So the boat was inoperable but still afloat. Via the last ssh terminal we could still run some simple commands like "ls" which of course resulted in an I/O error. All data on harddisk was zero, no secrets left to be compromised.<br /><br />In the end we tried to shutdown the system but failed. "shutdown" and "halt" just produced an "I/O error" message, and so did an "init". When we tried to start a "kill" command, something dropped the ssh connection at last. Finally we were locked out and lost control of the system.<br /><br />Our pings still have been answered for four days. On the fifth day, the hardware was retired and somebody had switched off the server. The boat had gone, now lying on the ground of the sea of oblivion.<br /></div><br /><a href="http://www.mister-wong.de/add_url/" onclick="'location.href=" action="addurl&bm_url=" bm_description="+encodeURIComponent(document.title)+" title="Diese Seite zu Mister Wong hinzufügen" target="_top"><img src="http://www.mister-wong.de/img/buttons/wongbig08.gif" alt="Diese Seite zu Mister Wong hinzufügen" border="0" /></a><script>digg_skin = 'compact'; </script><script src="http://digg.com/tools/diggthis.js" type="text/javascript"></script>Bernd Kuennenhttp://www.blogger.com/profile/09810163923342765495noreply@blogger.com0tag:blogger.com,1999:blog-8767709561112829005.post-5438064318652696962007-12-08T19:07:00.002+01:002008-02-18T22:34:04.221+01:00Pagination with DB2Modern web pages are widely using pagination, and the most common database in LAMP environment, MySQL, ist supporting this by the LIMIT <span style="font-style: italic;">offset, row_count</span> parameter. With DB2 one has to use a little trickery to come to a similar result.<br /><br />Imagine a list of blog entries, ordered by time, displaying the latest on top. This list is easily created by a VIEW:<br /><pre><br />Create View<br /> blog.vw_entries_500latest<br />as<br /> Select<br /> ROW_NUMBER() OVER(order by date,time desc) as ROWNO,<br /> id,date,time,title<br /> From<br /> blog.tb_entries_all<br /> order by<br /> date,time desc<br /> Fetch<br /> first 500 rows only;<br /></pre>Now lets load the data for the 11th page, where each page should list 25 blog entry titles:<pre><br />Select<br /> rowno,id,date,time,title<br />from<br /> blog.tb_entries_500latest<br />where<br /> rowno > 25 * (11 - 1)<br />fetch<br /> first 25 rows only ;<br /></prE>This should do the trick.<br /><br /><br /><a href="http://www.mister-wong.de/add_url/" onclick="'location.href=" action="addurl&bm_url=" bm_description="+encodeURIComponent(document.title)+" title="Diese Seite zu Mister Wong hinzufügen" target="_top"><img src="http://www.mister-wong.de/img/buttons/wongbig08.gif" alt="Diese Seite zu Mister Wong hinzufügen" border="0" /></a><script>digg_skin = 'compact'; </script><script src="http://digg.com/tools/diggthis.js" type="text/javascript"></script>Bernd Kuennenhttp://www.blogger.com/profile/09810163923342765495noreply@blogger.com0tag:blogger.com,1999:blog-8767709561112829005.post-47956433147346538342007-12-05T22:27:00.002+01:002008-02-18T22:34:32.895+01:001st business day in monthQuiet often events are logged in databases for the purpose of statistics or billing. Maybe there's the need to know which day of the month was the first business day. Here's a way to find out with DB2.<br /><br />Imagine a table log.events, containing a column named <i>when</i> of type date. Basically you'll rely on the function DayOfWeek(), which returns a minimum of 1 in case the given date is sunday, and a maximum of 7 for saturday:<br /><pre><br />select<br /> ( 15 - ( DayOfWeek( when - (Day(when) - 1) days) ) ) / 7<br /> +<br /> ( 2 * ( DayOfWeek( when - (Day(when) - 1) days) / 7 ) )<br />from<br /> log.events ;<br /></pre><br />With some arithmetic trickery the following code returns<ul><br /><li> 3 (1st of month is saturday, 1st business day is monday 3rd)<br /><li> 2 (1st of month is sunday, 1st business day is monday 2rd)<br /><li> 1 (1st of month and 1st business day is within tuesday to friday)<br /></ul><br /><a href="http://www.mister-wong.de/add_url/" onclick="'location.href=" action="addurl&bm_url=" bm_description="+encodeURIComponent(document.title)+" title="Diese Seite zu Mister Wong hinzufügen" target="_top"><img src="http://www.mister-wong.de/img/buttons/wongbig08.gif" alt="Diese Seite zu Mister Wong hinzufügen" border="0" /></a><script>digg_skin = 'compact'; </script><script src="http://digg.com/tools/diggthis.js" type="text/javascript"></script>Bernd Kuennenhttp://www.blogger.com/profile/09810163923342765495noreply@blogger.com0tag:blogger.com,1999:blog-8767709561112829005.post-26506596782598755102007-11-25T15:42:00.002+01:002008-02-18T22:34:52.533+01:00Advanced caching with mod_expires<div style="text-align: justify;"><span style="font-family:verdana;">The fastest (and cheapest) transfer of data is that which doesn't have to be done - because the data is already there. Or still there - the caching of files is widely known and practised. Each web browser is caching the files he formerly requested from the web server. But if the file is to be loaded a second time, still a request is sent to the web server which then usually is answering with a "304 Not modified" (see also in RFC <a href="http://www.w3.org/Protocols/rfc2616/rfc2616-sec10.html" target="_new">2616</a>) as can be seen in his access log:<br /></span><br /><div style="text-align: left;"><span style="font-family:courier new;"> 215.82.12.78 - - [02/Nov/2007:10:20:33 +0100] "GET /images/logo.gif HTTP/1.1" 304 -</span><br /></div><span style="font-family:verdana;"><br />Receiving this, finally the browser is loading the file from his cache. Instead of transfering the file just a couple of bytes went through the cables. But if a page uses lots of cached files like gif images and javascript files, even those 304 requests sum up - especially on connections with high latency.</span><br /><br /><span style="font-family:verdana;">To get rid of this unnecessary load the Apache web server is providing the module <a href="http://httpd.apache.org/docs/2.2/mod/mod_expires.html" target="_new">mod_expires</a> which enables you to stamp each delivered file with a kind of "valid until" mark. Therefore and according to the HTTP/1.1 specifications (subsection <a href="http://www.w3.org/Protocols/rfc2616/rfc2616-sec14.html" target="_new">14.21</a>), one line like the following is added to the http response header:</span><br /><br /><span style="font-family:courier new;"> Expires: Thu, 01 Mar 2007 09:30:00 GMT</span><br /><br /><span style="font-family:verdana;">This would cause the browser not to ask again for this document or file unless he looses it from his cache. Now lets have a closer look to the directives of mod_expires and the resulting possibilities. Here's an example:</span><br /><br /><span style="font-family:courier new;"> ExpiresActive On</span><br /><span style="font-family:courier new;"> ExpiresDefault "access plus 5 minutes"</span><br /><span style="font-family:courier new;"> ExpiresByType image/gif "access plus 2 days"</span><br /><span style="font-family:courier new;"> ExpiresByType text/html "modification plus 5 minutes"</span><br /><br /><span style="font-family:verdana;">ExpiresActive is used to enable or disable (on/off) the modification of the http response header. ExpiresDefault gives a default value for all documents, which are not captured by a rule on their own. Here the expiration date will be five minutes since time of access. This ensures that the document isn't reloaded by the client every few seconds while on the other hand he'll get the newer version within reasonable time, should the document be changed on the server.</span><br /><br /><span style="font-family:verdana;">ExpiresByType is giving you the possibility to control the expiration based on mime types. In the example gif images won't be reloaded from the server for 24 hours from of the point of downloading - based on the assumption that the gif images aren't changing but rather would be replaced by new files with new names. If your site is using gif buttons with roll over effects this takes lots of requests off your apache. On the other hand: If the layout of the web page and the gif images are changed while the names of the images remain the same, this would lead to rather strange looking pages in browsers which firstly accessed the page less than 24 hours ago. So this directive, though it offers a lot of reduction of traffic, is also to be handled with care.</span><br /><br /><span style="font-family:verdana;">In the example html pages won't be reloaded if their content is younger than five minutes. Imagine a highly frequented front page of a web portal which is generated every five minutes from dynamic content but saved as static html file for reasons of performance. If this was managed by the "access" alternative, a client that just loaded the 4:59 minutes old page would miss the newer version for five minutes. Based on "modification" it's ensured that nobody would miss the latest news.</span><br /><br /><span style="font-family:verdana;">In short, use the access rule for content that doesn't change (or at least not frequently). If modifications are necessary, try to use a new name. Use the modification rule for content that often is modified or for what reasons ever has a short lifetime.</span><br /><br /><span style="font-family:verdana;">Last but not least, this way you don't only reduce the load on your apache but also speed up your web application on client side: images and javascripts that don't have to be asked for every time they are to be used maybe displayed just some fractions of a second faster. But in the end (and at least subjectively) it all sums up.</span><br /></div><br /><a href="http://www.mister-wong.de/add_url/" onclick="'location.href=" action="addurl&bm_url=" bm_description="+encodeURIComponent(document.title)+" title="Diese Seite zu Mister Wong hinzufügen" target="_top"><img src="http://www.mister-wong.de/img/buttons/wongbig08.gif" alt="Diese Seite zu Mister Wong hinzufügen" border="0" /></a><script>digg_skin = 'compact'; </script><script src="http://digg.com/tools/diggthis.js" type="text/javascript"></script>Bernd Kuennenhttp://www.blogger.com/profile/09810163923342765495noreply@blogger.com0tag:blogger.com,1999:blog-8767709561112829005.post-42135723055203505442007-11-05T20:23:00.002+01:002008-02-18T22:35:21.562+01:00DB2: Converting Unix timeSometimes you've got to work with POSIX time, which might be known more commonly as UNIX time (<a href="http://en.wikipedia.org/wiki/Unix_time">http://en.wikipedia.org/wiki/Unix_time</a>). While some newer systems already use a 64bit value, the standard Unix time_t usually is a signed 32bit integer which represents the seconds elapsed since 1970-01-01UTC00:00:00. <br /><br />Assuming in your DB2 database there's sample.table with a column containing t_time and named the same. A conversion from time_t to a DB2 timestamp could be done like this:<br /><pre>SELECT<br /> DISTINCT time_t,<br /> <b>timestamp('1970-01-01-00.00.00') + (time_t) seconds</b><br /> as calculated_timestamp<br />FROM<br /> sample.table<br />WHERE<br /> time_t=946684800<br />;</pre>The result would look like the following:<br /><br /><pre>TIME_T CALCULATED_TIMESTAMP<br />----------- --------------------------<br /> 946684800 2000-01-01-00.00.00.000000<br /><br /> 1 record(s) selected.</pre><br />This is also useful for changing the time value permanently from time_t to timestamp when working with DB2 on Unix log data. Daily logs containing time_t could be loaded into a temporary table in the first step. When adding the data to the permament table (working with a timestamp column) , a load is to be done from a cursor which has to be defined as SELECT using the above mentioned conversion.<br /><a href="http://www.mister-wong.de/add_url/" onclick="'location.href=" action="addurl&bm_url=" bm_description="+encodeURIComponent(document.title)+" title="Diese Seite zu Mister Wong hinzufügen" target="_top"><img src="http://www.mister-wong.de/img/buttons/wongbig08.gif" alt="Diese Seite zu Mister Wong hinzufügen" border="0" /></a><script>digg_skin = 'compact'; </script><script src="http://digg.com/tools/diggthis.js" type="text/javascript"></script>Bernd Kuennenhttp://www.blogger.com/profile/09810163923342765495noreply@blogger.com0tag:blogger.com,1999:blog-8767709561112829005.post-9132507988386698572007-10-25T18:40:00.002+02:002008-02-18T22:35:47.133+01:00DB2: Automatic line numberingWhen generating reports with lots of lines, sometimes it's nice to have them numbered automatically. For this DB2 offers the ROW_NUMBER() OVER() functionality. The first part numbers the rows in the order they are passed from the retrieving process. Therefore, if you're using the ORDER BY clause for displaying the resultset, you may get line numbers but in no special order. You'll need to pass the ordering directive to the second part OVER().<br /><br />Here's an example:<pre><br />Select<br /> ROW_NUMBER() OVER(order by NMBR asc) as ROW,<br /> NMBR<br />from<br /> lucky.numbers<br />order by<br /> NMBR asc<br /><br />ROW NMBR<br />-------------------- ----------<br /> 1 123<br /> 2 456<br /> 3 2345<br /> 4 12345<br /> <br /> 4 record(s) selected.<br /></pre><br /><a href="http://www.mister-wong.de/add_url/" onclick="'location.href=" action="addurl&bm_url=" bm_description="+encodeURIComponent(document.title)+" title="Diese Seite zu Mister Wong hinzufügen" target="_top"><img src="http://www.mister-wong.de/img/buttons/wongbig08.gif" alt="Diese Seite zu Mister Wong hinzufügen" border="0" /></a><script>digg_skin = 'compact'; </script><script src="http://digg.com/tools/diggthis.js" type="text/javascript"></script>Bernd Kuennenhttp://www.blogger.com/profile/09810163923342765495noreply@blogger.com0tag:blogger.com,1999:blog-8767709561112829005.post-46647345008759079492007-10-21T20:37:00.002+02:002008-02-18T22:36:30.257+01:00Case insensitive indicesIn my experience DB2 is one of the most correctly working databases. Where others are, lets say, a bit more flexible, DB2 follows the given path down to the last bit. An example we just fell over: Case sensitivity.<br /><br />These days we installed a new product which usually works with a database system made in Redmond. We wanted it to work with DB2 and were told that - in theory - there should be no big problem. Praxis showed that there's always Murphys law awaiting you.<br /><br />At one point the software does a query with condition like „WHERE upper(NAME)='ABCD' “. While in the beginning there was no problem, we found that several data imports later this little phrase slowed down the whole system. The cause: Although there was a (case sensitive) index on the NAME column, DB2 had to do an upper() on every single row.<br /><br />While DB2 v9 for z/OS allows you to build an index using the upper() function, DB2 v8 doesn't give you this possibility (yet). But there's a workaround. Here's how we got around.<br /><br />First, We added a generated column, containing the upper case text:<br /><pre><br />-- alter table<br />SET INTEGRITY FOR A001.FOOBAR OFF ;<br /><br />alter table A001.FOOBAR<br /> add column NAME_UP<br /> GENERATED ALWAYS AS ( UPPER(NAME) ) ;<br /><br />-- enable integrity again and fill column; may take a while<br />SET INTEGRITY FOR A001.FOOBAR<br /> IMMEDIATE CHECKED FORCE GENERATED ;<br /></pre><br />Then, we added an index on the newly created column:<br /><pre><br />-- drop old index; create an index using new upper_column<br />DROP INDEX A001.FOOBAR_IDX1;<br />CREATE INDEX A001.FOOBAR_IDX1 ON FOOBAR ( NAME_UP, XID, YID );<br /></pre><br />After that, the internal optimizer recognized that it could use the GENERATED column and its index, and the import jobs returned to the high velocity they had shown in their very first moments: Instead of hours or even days, everything was done within a couple of minutes.<br /><a href="http://www.mister-wong.de/add_url/" onclick="'location.href=" action="addurl&bm_url=" bm_description="+encodeURIComponent(document.title)+" title="Diese Seite zu Mister Wong hinzufügen" target="_top"><img src="http://www.mister-wong.de/img/buttons/wongbig08.gif" alt="Diese Seite zu Mister Wong hinzufügen" border="0" /></a><script>digg_skin = 'compact'; </script><script src="http://digg.com/tools/diggthis.js" type="text/javascript"></script>Bernd Kuennenhttp://www.blogger.com/profile/09810163923342765495noreply@blogger.com0tag:blogger.com,1999:blog-8767709561112829005.post-79435665353138928052007-10-15T19:12:00.000+02:002007-10-16T13:46:49.206+02:00Long running transactionsFrequently there are jobs to do on the database which affect quiet a couple of rows, like deleting yesterdays log events from the log table, turning into a long running transaction. While being executed, this may lead to certain lock events, blocking other processes. So what to do about those long runners?<br /><br />Rule #1 above all others: Avoid long runners. This may be done by turning them into a couple of short runners. Based on the above example, a delete of yesterdays events could be broken down to 24 steps, starting with a<br /><span style="font-size:85%;"><span style="font-family:courier new;">"DELETE ... WHERE event_date=(current_date-1 day) AND event_time>='23:00:00' "</span></span><br />and ending with<br /><span style="font-size:85%;"><span style="font-family:courier new;">"... even_time>='00:00:00' ".</span></span><br /><br />If there's no possibility to go that way, the execution may gain speed by one or more of the following steps:<br /><ol style="list-style-type: decimal;"><li>Try to do this in off peak hours to have more resources for your long runner.</li><li> Increase the size of the buffer pool(s) and the NUM_IOCLEANERS if necessary/possible.</li><li>Log files should not be placed on the same physical file system as the database itself.</li><li>Sometimes increasing the parameter LOGBUFSZ has a possitive effect. (Attention: LOGBUFSZ is allocated within DBHEAP so you have to change this value, too.)<br /></li></ol>For point 2-4 you'll need DB2 admin rights on the database. Besides, moving the log files to a different disk often improves all DB2 write actions to the specific database, and so does adding memory to the buffer pools. But when adding memory to those, try to add it step by step and monitor the gain of performance. Usually you'll get to a breaking point somewhere when adding memory doesn't earn you much performance any more. You may also use the high water marks and the <span style="font-style: italic;">log_space_used</span> value when monitoring the usage of memory.Bernd Kuennenhttp://www.blogger.com/profile/09810163923342765495noreply@blogger.com0tag:blogger.com,1999:blog-8767709561112829005.post-34277078229123905252007-08-15T19:59:00.000+02:002007-08-16T17:30:38.533+02:00DB2: Internal functions won't run<span style="font-size:100%;"><span style="font-family:verdana;">They say that time and space are relative. If knowing Einstein and having a voice, my computer would confirm this, for that at times its disc space is relatively short. </span><br /><br /><span style="font-family:verdana;">DB2 sometimes has a rather complex relationship to time itself, too. Once we changed the system time on a Linux based DB2 test server, and subsequently forgot about this. Next day, a developer called us and told us that some of his sql statements failed where they still had been working several days ago.</span><br /><br /><span style="font-family:verdana;">Our investigations showed that when containing an internal function, like in "db2 values ucase('hello') ", DB2 gave us the following error message:</span><br /><br /><span style="font-family:courier new;">SQL0440N No authorized routine named "UCASE" of type "FUNCTION" having </span><span style="font-family:courier new;">compatible arguments was found. SQLSTATE=42884 </span><br /><br /><span style="font-family:verdana;">This error appeared on all existing databases in that instance. After creating a new database, we found that only the old ones were affected.</span><br /><br /><span style="font-family:verdana;">We opened a PMR with IBM and the support told us that sometimes this may happen when changing the system time. (Maybe we moved it </span></span><span style="font-size:100%;"><span style="font-family:verdana;">too far?) </span></span><span style="font-size:100%;"><span style="font-family:verdana;">There's a DB2 tool called 'fixfunc' available at IBM support. We tried it and it succeeded - all databases were usable afterwards.</span><br /><br /><br /><br /></span>Bernd Kuennenhttp://www.blogger.com/profile/09810163923342765495noreply@blogger.com0tag:blogger.com,1999:blog-8767709561112829005.post-35552237000296613282007-05-31T03:00:00.000+02:002007-08-15T15:37:15.717+02:00DB2 Express-C v9 for Power architecture<div align="justify"><span style="font-family:verdana;">Since v8, the freely distributable version of IBMs database software, DB2 Express-C, is available for Windows and Linux, the latter supporting both intel and power architecture. Several days ago, I was looking for v9 to download, but couldn't find it. All the download list offered was v9 for Windows and Linux on x86 and ia64 systems.</span></div><div align="justify"><br /><span style="font-family:verdana;">After writing an email to </span><a href="mailto:db2x@ca.ibm.com"><span style="font-family:verdana;">db2x@ca.ibm.com</span></a><span style="font-family:verdana;">, Ryan Chase and Ian Hakes of the db2 express-c community team reacted and added the v9 ppc64 (power) version to the list again. It's available in the </span><a href="https://www14.software.ibm.com/webapp/iwm/web/preLogin.do?lang=en_US&source=dm-db2express&S_PKG=lnxppcv9&cp=UTF-8"><span style="font-family:verdana;">Express-C download area</span></a><span style="font-family:verdana;">. You 'll need to login before selecting the download.</span></div>Bernd Kuennenhttp://www.blogger.com/profile/09810163923342765495noreply@blogger.com0