[alert] AIX Posix Timezone issue

Maybe you did get or you will get an issue with the date command in AIX.

expected behavior, Linux

$ TZ=NZST-12NZDT,M10.1.0/2,M3.3.0/3 date
Sat Mar 17 00:14:54 NZDT 2012
$ TZ=Pacific/Auckland date
Sat Mar 17 00:14:58 NZDT 2012

unexpected behavior, AIX

$ TZ=Pacific/Auckland date
Sat Mar 17 00:15:50 GMT+13:00 2012
$ TZ=NZST-12NZDT,M10.1.0/2,M3.3.0/3 date
Fri Mar 16 23:15:52 NZST 2012

The consequence : date, and all other unix commands like ls, who, ps that display the date in human readable format, and all programs that use ctime are affected

$ TZ=NZST-12NZDT,M10.1.0/2,M3.3.0/3 perl -e 'use POSIX;print ctime(time)'
Fri Mar 16 23:19:51 2012

Reference and link to the fixes : www-01.ibm.com/support/docview.wss?uid=isg3T1013017

Close tabs in Toad

Sometimes you wait a feature for so long that even if it is small and common, you feel greatly happy

In Toad Greatly replace the “right-click” + “close tab”

Powershell and dates

I wrote about unix timestamp i powershell.

I wrote : It is chockingly easy !
but I should have written : … it is not correct 🙁

PS> ./perl -e "print time.'`n'"
PS> get-date -u %s

there is about 3605 seconds difference. 1 hour for Europe/Zurich and 5 seconds to type on a slow keyboard…

Even more confusing

PS> get-date "1970-01-01 00:00:00 +00:00" -u %s

Ok, why that? this is related to the Kind

PS> (get-date "1970-01-01 00:00:00 +00:00").kind

A Unix timestamp of Kind Local is useless. Don’t use this.

Unix timestamp should only refers to Utc.

PS> get-date
Sonntag, 11. März 2012 10:47:35
PS> get-date ((get-date).touniversaltime()) -u %s

still it is very confusing to using the unixformat in Windows

PS> get-date -u %c__%Z__%s
So Mrz 11 10:55:05 2012__+01__1331463305.85197
PS> get-date ((get-date).touniversaltime()) -u %c__%Z__%s
So Mrz 11 09:55:46 2012__+01__1331459746.07913

the %s does not work as expected with local (as it refers to a local 1970-01-01) and %Z does not work at all with utc (it sould be +00

OK, let’s switch to the Microsoft time. You count the time as the number of tenth of microseconds (or ticks) since 1 Jan 0001. Again it may differ if you do not use utc.

Let’s define constant $c as number of ticks until 1970-01-01, in utc

PS> $c = (get-date "1970-01-01 00:00:00 +00:00").touniversaltime().ticks
PS> $c

Let’s convert it to a human readable format

PS C:\> New-Object datetime($c)

Donnerstag, 1. Januar 1970 00:00:00

So far so good.

Lets convert 1331461119 back and forth

PS> (New-Object datetime($c + 1331461119 * 10000000)).tolocaltime()

Sonntag, 11. März 2012 11:18:39

PS> (New-Object datetime($c + 1331461119 * 10000000))

Sonntag, 11. März 2012 10:18:39
PS> ( (get-date "2012-03-11 10:18:39 +00:00").touniversaltime().ticks - $c ) / 10000000
PS> ( (get-date "2012-03-11 11:18:39 +01:00").touniversaltime().ticks - $c ) / 10000000

BTW, what does Oracle think of the number of ticks of current date ?

PS> $d=36217;"select to_char(date '0001-01-01' + $d,'YYYY-MM-DD') from dual;"|sqlplus -s -L scot/tiger;New-Object datetime($d*864000000000)


Sonntag, 28. Februar 0100 00:00:00

PS C:\> $d=36218;"select to_char(date '0001-01-01' + $d,'YYYY-MM-DD') from dual;"|sqlplus -s -L scot/tiger;New-Object datetime($d*864000000000)


Montag, 1. März 0100 00:00:00

Apparently Microsoft does not have a leap year in 100AD.

Ok, whatelse to say about date?

There is one more format that is commonly used in Microsoft world, the so-called DMTF or Distributed Management Task Force. Let’s have a quick look with 1-JAN-2000.

PS C:\> [System.Management.ManagementDateTimeConverter]::TodmtfDateTime((get-date "2000-01-01 00:00:00"))
PS C:\> [System.Management.ManagementDateTimeConverter]::TodmtfDateTime((get-date "2000-01-01 00:00:00 +00:00").touniversaltime())
PS C:\> [System.Management.ManagementDateTimeConverter]::ToDateTime('20000101000000.000000+060')
Samstag, 1. Januar 2000 00:00:00
PS C:\> [System.Management.ManagementDateTimeConverter]::ToDateTime('20000101000000.000000+000').touniversaltime()
Samstag, 1. Januar 2000 00:00:00

to ADO or to ODP ?

I just read on System.Data.OracleClient Namespace that System.Data.OracleClient are deprecated.
The types in System.Data.OracleClient are deprecated. The types are supported in version 4 of the .NET Framework but will be removed in a future release. Microsoft recommends that you use a third-party Oracle provider.

The alternative is to use ODP (from Oracle) instead of ADO (from Microsoft). More powerful of course.

ADO (within powershell):

PS C:\> [Reflection.Assembly]::LoadWithPartialName("System.Data.OracleClient")

GAC    Version        Location
---    -------        --------
True   v2.0.50727     C:\WINDOWS\assembly\GAC_32\system.data.oracleclient\\system.data.orac...

PS C:\> $connection=New-Object DATA.OracleClient.OracleConnection("Data Source=DB01;User Id=scott;Password=tiger")
PS C:\> $connection.Open()
PS C:\> (new-Object DATA.OracleClient.OracleCommand("select 'Hello World' from dual",$connection)).ExecuteScalar()
Hello World
PS C:\> $connection.Close()


PS C:\> [Reflection.Assembly]::LoadFile("C:\oracle\product\11.1.0\client_1\ODP.NET\bin\2.x\Oracle.DataAccess.dll")

GAC    Version        Location
---    -------        --------
True   v2.0.50727     C:\WINDOWS\assembly\GAC_32\Oracle.DataAccess\\Oracle.DataAccess.dll

PS C:\> $connection=New-Object Oracle.DataAccess.Client.OracleConnection("Data Source=DB01; User Id=scott; password=tiger")
PS C:\> (new-object Oracle.DataAccess.Client.OracleCommand("select 'helloworld' from dual",$connection)).executescalar()
PS C:\> $connection.close()
PS C:\> $connection.dispose()

Read more : Oracle Data Provider for .NET Developer’s Guide

PS: also possible

PS> $dataset = New-Object Data.dataset
PS> (New-Object Oracle.DataAccess.Client.OracleDataAdapter("select * from emp",$connection)).fill($dataset)
PS> ($dataset.tables[0]|where{ $_.empno -eq 7902 }).ename

on Windows Management Instrumentation

I have a bit versed into powershell over the last months, I just cannot stop discovering new gems.

Ex : Win32 Classes

Self-explanatory examples :

PS> (get-wmiobject win32_processor).currentclockspeed
PS> (get-wmiobject win32_operatingsystem).version
PS> (get-wmiobject win32_proxy).ProxyServer
PS> (get-wmiobject win32_proxy).ProxyPortNumber

it goes on and on and on… the ways those things are accessible from the prompt in interactive command line mode or within a script is amazing me

scott.emp in Sybase

As part of my dba job, I have a few Sybase DBs around. I created the well known Oracle Scott tables to play with

$ isql -U sa -P ""
1> sp_addlogin "scott","tiger7"
2> go
Password correctly set.
Account unlocked.
New login created.
(return status = 0)
1> create database lsc01
2> go
CREATE DATABASE: allocating 1536 logical pages (6.0 megabytes) on disk 
  'data_dev' (1536 logical pages requested).
Database 'lsc01' is now online.
1> use lsc01
2> go
1> sp_adduser scott
2> go
New user added.
(return status = 0)
1> grant create table to scott
2> go
1> exit
$ isql -U scott -P tiger7 -D lsc01
1> create table dept(deptno int constraint pk_dept primary key, dname varchar(14), loc varchar(13))
2> go
1> insert into dept values(10,'ACCOUNTING','NEW YORK')
2> go
(1 row affected)
1> insert into dept values(20,'RESEARCH','DALLAS')
2> go
(1 row affected)
1> insert into dept values(30,'SALES','CHICAGO')
2> go
(1 row affected)
1> insert into dept values(40,'OPERATIONS','BOSTON')
2> go
(1 row affected)
1> create table emp(empno int constraint pk_emp primary key, ename varchar(10), job varchar(9), mgr int null, hiredate date, sal float, comm float null, deptno int constraint fk_dept references dept)
2> go
1> insert into emp values(7369,'SMITH','CLERK',7902,'1980-12-17',800,null,20)
2> go
(1 row affected)
1> insert into emp values(7499,'ALLEN','SALESMAN',7698,'1981-02-20',1600,300,30)
2> go
(1 row affected)
1> insert into emp values (7521,'WARD','SALESMAN',7698,'1981-02-22',1250,500,30)
2> go
(1 row affected)
1> insert into emp values (7566,'JONES','MANAGER',7839,'1981-04-02',2975,NULL,20)
2> go
(1 row affected)
1> insert into emp values (7654,'MARTIN','SALESMAN',7698,'1981-09-28',1250,1400,30)
2> go
(1 row affected)
1> insert into emp values (7698,'BLAKE','MANAGER',7839,'1981-05-01',2850,NULL,30)
2> go
(1 row affected)
1> insert into emp values (7782,'CLARK','MANAGER',7839,'1981-06-09',2450,NULL,10)
2> go
(1 row affected)
1> insert into emp values (7788,'SCOTT','ANALYST',7566,'1987-07-13',3000,NULL,20)
2> go
(1 row affected)
1> insert into emp values (7839,'KING','PRESIDENT',NULL,'1981-11-17',5000,NULL,10)
2> go
(1 row affected)
1> insert into emp values (7844,'TURNER','SALESMAN',7698,'1981-09-08',1500,0,30)
2> go
(1 row affected)
1> insert into emp values (7876,'ADAMS','CLERK',7788,'1987-07-13',1100,NULL,20)
2> go
(1 row affected)
1> insert into emp values (7900,'JAMES','CLERK',7698,'1981-12-03',950,NULL,30)
2> go
(1 row affected)
1> insert into emp values (7902,'FORD','ANALYST',7566,'1981-12-03',3000,NULL,20)
2> go
(1 row affected)
1> insert into emp values (7934,'MILLER','CLERK',7782,'1982-01-23',1300,NULL,10)
2> go
(1 row affected)
1> select ename,dname,d.deptno,empno from dept d left join emp e on (d.deptno=e.deptno)
2> go
 ename                dname                        deptno      empno       
 -------------------- ---------------------------- ----------- ----------- 
 KING                 ACCOUNTING                            10        7839 
 CLARK                ACCOUNTING                            10        7782 
 MILLER               ACCOUNTING                            10        7934 
 FORD                 RESEARCH                              20        7902 
 SCOTT                RESEARCH                              20        7788 
 JONES                RESEARCH                              20        7566 
 ADAMS                RESEARCH                              20        7876 
 SMITH                RESEARCH                              20        7369 
 WARD                 SALES                                 30        7521 
 BLAKE                SALES                                 30        7698 
 ALLEN                SALES                                 30        7499 
 JAMES                SALES                                 30        7900 
 TURNER               SALES                                 30        7844 
 MARTIN               SALES                                 30        7654 
 NULL                 OPERATIONS                            40        NULL 

(15 rows affected)
1> commit
2> go

Sql file to download : emp-dept.sql

There are obviously a few differences, the password is associated with a login that belongs to the “db server”, and the user is created in the database “lsc01” and associated to the login. In Sybase, one database server (typically one Unix Process), contains more than one database. There is also a master database, which is as sensitive as your system tablespace. Still in the official Sybase administrator course, they already teach you how to manually update the dictionary…

There is no varchar2 or number, and the default for a column is not null. The equivalent of DESC EMP is sp_help emp, but it is more verbose. The columns are case-sensitive, create table EMP is not the same as create table emp.

Not all “ANSI” code will work, like DATE ‘2000-01-01’, FULL OUTER JOIN, USING and all those Oracle “ANSI” things are not meant “COMPATIBLE WITH SYBASE” but more “CONFORM TO ANSI”

ok, that said, it was pretty straightforward