SAP Sybase IQ – dbcc for IQ? Yes! Just use sp_iqcheckdb

If you’re coming from Microsoft SAP SybaseSQL Server or Sybase ASE, SAP’s IQ doesn’t have the slew of dbcc commands you might expect. It rolls up all the pertinent dbccs into a single stored procedure. sp_iqcheckdb has four operational modes with a granularity at the database, table or index level:

  1. In check mode, sp_iqcheckdb performs an internal consistency check on all IQ indexes and checks that each database block has been allocated correctly. All available database statistics are reported. This mode reads all data pages and can detect all types of allocation problems and most types of index inconsistencies. Check mode should run considerably faster than verify mode for most databases.
  2. In verify mode, sp_iqcheckdb performs an intra-index consistency check, in addition to internal index consistency and allocation checking. All available database statistics are reported. The contents of each non-FP index is verified against its corresponding FP index(es). Verify mode reads all data pages and can detect all types of allocation problems and all types of index inconsistencies.
  3. In allocation mode, sp_iqcheckdb checks that each database block is allocated correctly according to the internal physical page mapping structures (blockmaps). Database statistics pertaining to allocation are also reported. This mode executes very quickly. Allocation mode, however, does not check index consistency and cannot detect all types of allocation problems.
  4. When the Sybase IQ server runs in single-node mode, you can use dropleaks mode with either a database or dbspace target to reset the allocation map for the entire database or specified dbspace targets. If the target is a dbspace, then the dropleaks operation must also prevent read-write operations on the named dbspace. All dbspaces in the database or dbspace list must be online.

No changes are actually performed with the exception of the dropleaks mode. If check or verify detects an issue with allocation, you will need to run sp_iqcheckdb in the dropleaks mode while the database is in single user mode. For any issues with indexes, use sp_rebuildindex on both the index and possibly the affected column of the table.

By default output of sp_iqcheckdb will go to the (instance_name).iqmsg log file but you can redirect it to its own file for easier reading:

set temporary option dbcc_log_progress='on';
sp_iqcheckdb 'verify database' ># filename

Please note that the file name may not contain spaces or special symbols and the path will be local to the IQ box. So, unless you want to hunt for your checkdb output file, please specify the full path. No, you can not put the filename in any type of quotes and you can’t escape interesting characters.

Unless you’re under a serious time constraint, always run sp_iqcheckdb in verify mode to determine structural issues with your IQ databases.

Share Button

Connect to Microsoft SQL Server (including Azure instances) from Linux (x86/x86-64) using ODBC and JDBC

Connecting to Microsoft SQL Server from Linux can be done through two different methods: ODBC and JDBC. Unfortunately, FreeTDS doesn’t connect to the newest versions of SQL Server unless you want to enable legacy connections.

The Microsoft JDBC Driver 4.0 for SQL Server, a Type 4 JDBC driver provides database connectivity through the standard JDBC application program interfaces (APIs) available in Java Platform, Enterprise Edition 5 and 6.

The Microsoft ODBC Driver (Linux) For SQL Server provides native connectivity from Windows to Microsoft SQL Server and Windows Azure SQL Database on Linux.

Share Button

Sybase SQL Server 11.0.3.3 for Linux – still going strong

Last night I helped a friend at a small company install, configure and tune Sybase SQL Server 11.0.3.3 for Linux using the same method as I used on HOWTO: INSTALLING AND RUNNING SAP / SYBASE SQL SERVER 11.0.3.3 ESD 6 ON UBUNTU LINUX USING KVM. No, I didn’t provide the software, they already had it.

There is something refreshing going to sybinit using ctrl-a to go to the next screen.  No X-Windows needed.  No Java.  Just a simple DBMS doing DBMS work. 

Share Button

HOWTO: Installing and running SAP / Sybase SQL Server 11.0.3.3 esd 6 on Ubuntu Linux using KVM

You can run KVM on just about any modern Linux distribution provided that your host is capable of providing virtualization. The folks over at HowtoForge go through the steps for Ubuntu 12.10.

What you will need for the KVM virtual machine:

  • Ubuntu Server 6.06.2 for Intel x86
  • Sybase SQL Server 11.0.3.3 esd 6 for Linux 32bit (if anyone knows of a location to legally download these let me know)

I used virt-viewer to create the virtual machine but you can create the xml file yourself if you want to. Make sure you specify:

  • Disk bus: IDE using native threads and no caching
  • 1GB of RAM or more
  • VNC or Spice display

my Sybase_11033.xml file:

<domain type='kvm'>
  <name>Sybase_11033</name>
  <uuid>85ffed98-fd6e-8475-37b0-219359c89c25</uuid>
  <description>Sybase SQL Server 11.0.3.3 on Ubuntu 6.06.02 (Dapper Drake)</description>
  <memory unit='KiB'>1048576</memory>
  <currentmemory unit='KiB'>1048576</currentmemory>
  <vcpu placement='static'>1</vcpu>
  <os>
    <type arch='x86_64' machine='pc-1.2'>hvm</type>
    <boot dev='hd'></boot>
  </os>
  <features>
    <acpi></acpi>
    <apic></apic>
    <pae></pae>
  </features>
  <clock offset='utc'></clock>
  <on_poweroff>destroy</on_poweroff>
  <on_reboot>restart</on_reboot>
  <on_crash>restart</on_crash>
  <devices>
    <emulator>/usr/bin/kvm-spice</emulator>
    <disk type='file' device='disk'>
      <driver name='qemu' type='qcow2' cache='none' io='threads'></driver>
      <source file='/var/lib/libvirt/images/Sybase_11033.img'/>
      <target dev='hda' bus='ide'></target>
      <address type='drive' controller='0' bus='0' target='0' unit='0'></address>
    </disk>
    <disk type='file' device='cdrom'>
      <driver name='qemu' type='raw'></driver>
      <source file='/home/jason/iso/ubuntu-6.06.2-server-i386.iso'/>
      <target dev='hdb' bus='ide'></target>
      <readonly></readonly>
      <address type='drive' controller='0' bus='0' target='0' unit='1'></address>
    </disk>
    <controller type='usb' index='0'>
      <address type='pci' domain='0x0000' bus='0x00' slot='0x01' function='0x2'></address>
    </controller>
    <controller type='ide' index='0'>
      <address type='pci' domain='0x0000' bus='0x00' slot='0x01' function='0x1'></address>
    </controller>
    <controller type='virtio-serial' index='0'>
      <address type='pci' domain='0x0000' bus='0x00' slot='0x04' function='0x0'></address>
    </controller>
    <interface type='bridge'>
      <mac address='52:54:00:59:e1:78'></mac>
      <source bridge='br0'/>
      <address type='pci' domain='0x0000' bus='0x00' slot='0x03' function='0x0'></address>
    </interface>
    <serial type='pty'>
      <target port='0'></target>
    </serial>
    <console type='pty'>
      <target type='serial' port='0'></target>
    </console>
    <channel type='spicevmc'>
      <target type='virtio' name='com.redhat.spice.0'></target>
      <address type='virtio-serial' controller='0' bus='0' port='1'></address>
    </channel>
    <input type='mouse' bus='ps2'/>
    <graphics type='spice' autoport='yes'></graphics>
    <video>
      <model type='qxl' vram='65536' heads='1'></model>
      <address type='pci' domain='0x0000' bus='0x00' slot='0x02' function='0x0'></address>
    </video>
    <memballoon model='virtio'>
      <address type='pci' domain='0x0000' bus='0x00' slot='0x05' function='0x0'></address>
    </memballoon>
  </devices>
</domain>

Go through the install of Ubuntu Server 6.06.2. After it is completed, you will need to modify a few files:

Replace /etc/apt/sources.list with:

deb http://old-releases.ubuntu.com/ubuntu/ dapper main restricted universe multiverse
deb http://old-releases.ubuntu.com/ubuntu/ dapper-updates main restricted universe multiverse
deb http://old-releases.ubuntu.com/ubuntu/ dapper-security main restricted universe multiverse
sudo apt-get update
sudo apt-get upgrade
sudo apt-get install alien

Modify /boot/grub/menu.lst adding elevator=deadline:

## additional options to use with the default boot option, but not with the
## alternatives
## e.g. defoptions=vga=791 resume=/dev/hda5
 defoptions=quiet splash elevator=deadline

## altoption boot targets option
## multiple altoptions lines are allowed
## e.g. altoptions=(extra menu suffix) extra boot options
##      altoptions=(recovery mode) single
# altoptions=(recovery mode) single

## controls how many kernels should be put into the menu.lst
## only counts the first occurence of a kernel, not the
## alternative kernel options
## e.g. howmany=all
##      howmany=7
# howmany=all

## should update-grub create memtest86 boot option
## e.g. memtest86=true
##      memtest86=false
# memtest86=true

## should update-grub adjust the value of the default booted system
## can be true or false
# updatedefaultentry=false

## ## End Default Options ##

title		Ubuntu, kernel 2.6.15-51-server
root		(hd0,0)
kernel		/boot/vmlinuz-2.6.15-51-server root=/dev/hda1 ro quiet splash elevator=deadline
initrd		/boot/initrd.img-2.6.15-51-server
savedefault
boot

Modify /etc/fstab to increase the size of tmpfs and to set noatime,nodiratime:

/dev/hda1       /               ext3    rw,async,noatime,nodiratime,errors=remount-ro 0       1
tmpfs		/dev/shm        tmpfs   size=900M   0 0

Modify /etc/sysctl.conf to set up the shared memory and virtual memory settings:

vm.overcommit_memory=1
vm.swappiness=5
kernel.shmmax=1073741824
kernel.shmall=1073741824

OPTIONAL: Install samba and winbind so you want to connect to your vm by name but don’t want to set up dns:

sudo apt-get install samba winbind

Modify /etc/nsswitch.conf:

# /etc/nsswitch.conf
#
# Example configuration of GNU Name Service Switch functionality.
# If you have the `glibc-doc' and `info' packages installed, try:
# `info libc "Name Service Switch"' for information about this file.

passwd:         compat
group:          compat
shadow:         compat

hosts:          files dns mdns wins
networks:       files

protocols:      db files
services:       db files
ethers:         db files
rpc:            db files

netgroup:       nis

OPTIONAL (needed for samba/winbind) Modify /etc/samba/smb.conf:

# add netbios name
netbios name = sybase-11033
# modify name resolve order
   name resolve order = bcast lmhosts host wins

*RESTART* the VM

Convert the Sybase RPMs to debian packages (dpkg):

sudo alien -d *.rpm

Install Sybase SQL Server:

sudo dpkg -i *.deb

This will install the software into /opt/sybase.

Add a sybase os user and change the owner of /opt/sybase to the sybase user:

sudo adduser --home /opt/sybase --shell /bin/bash --no-create-home sybase
sudo chown -R sybase:sybase /opt/sybase

I created a /opt/sybase/.bash_profile script but you can also put this in /etc/profile if you wish:

export SYBASE=/opt/sybase

export SYBPLATFORM=linux
export LD_LIBRARY_PATH=$SYBASE/lib
export LC_ALL=default
unset LANG

export PATH=$SYBASE/bin:$PATH

Start the Sybase configuration by running as the sybase user:

$SYBASE/install/sybinit

I would recommend a 100MB or larger master device and sybsystemprocs device for 11.0.3.3. It will install just fine but if you try to start Sybase with more the 32MB of memory, it will fail. If you want to increase it you need to do two things:

  1. Increase max shared memory (kernel.shmmax and kernel.shmall) in /etc/sysctl.conf – which we already did
  2. Set the stack size to unlimited

Setting the stack size to unlimited is easy so in your RUN_server file:

#!/bin/sh
#
# Adaptive Server Information:
#  name:                          SYBASE_11033
#  master device:                 /opt/sybase/master.dat
#  master device size:            76800
#  errorlog:                      /opt/sybase/install/errorlog
#  interfaces:                    /opt/sybase
#

ulimit -s unlimited

/opt/sybase/bin/dataserver -d/opt/sybase/master.dat -sSYBASE_11033 \
-e/opt/sybase/install/errorlog -i/opt/sybase -c/opt/sybase/SYBASE_11033.cfg 

Since this is SQL Server 11.0.3.3, we don’t have to mess with LD_POINTER_GUARD.

Screenshot from 2013-03-10 16:05:31

Why you would use such an old DBMS version? You might have software that requires this version and the software vendor went out of business or discontinued the software with no upgrade path. In any case, Sybase’s SQL Server 11.0.3.3 is still useful.

Share Button

HOWTO: Building Perl module DBD::Sybase 1.14 on Windows (32bit or 64bit) with ActiveState Perl 5.16, Microsoft Visual Studio and Sybase OpenClient 15.7

Compiling the DBD::Sybase Perl module really requires Microsoft Visual C++ 2005 or higher. To get started open the “Visual Studio 2005 Command Prompt”.Visual Studio 2005 Command Prompt

You will need to fix the Makefile.PL file:

if ( $^O eq 'MSWin32' ) {
  $lib_string = "-L$SYBASE/lib -llibct.lib -llibcs.lib -llibtcl.lib -llibcomn.lib -llibintl.lib -llibblk.lib $attr{EXTRA_LIBS} -lm";

to

if ( $^O eq 'MSWin32' ) {
  $lib_string = "-L$SYBASE/lib -llibsybct.lib -llibsybcs.lib -llibsybblk.lib $attr{EXTRA_LIBS}";

If you don’t, nmake won’t be able to link against the Sybase libraries. Note that we’re adding “syb” after “lib”.

Warning (mostly harmless): No library found for -llibct.lib
Warning (mostly harmless): No library found for -llibcs.lib
Warning (mostly harmless): No library found for -llibtcl.lib
Warning (mostly harmless): No library found for -llibcomn.lib
Warning (mostly harmless): No library found for -llibintl.lib
Warning (mostly harmless): No library found for -llibblk.lib
Warning (mostly harmless): No library found for -lm

When you run perl Makefile.PL, choose the defaults because the nmake test will NOT work with Visual Studio.
Next we need to change lines 3915 and 3916 in dbdimp.c because C89 requires that declarations of variables must occur at the beginning of a code block. This is part of the C89 specification.

for (i = 0; i < foundOutput; i++) { phs = params[i].phs; CS_DATAFMT datafmt;[/c] to [c num=1 highlight_lines = "2,3"]for (i = 0; i < foundOutput; i++) { CS_DATAFMT datafmt; phs = params[i].phs;[/c] If you don't we will get the following errors: [text]dbdimp.c(3916) : error C2275: 'CS_DATAFMT' : illegal use of this type as an expression C:\Sybase\OCS-15_0\include\cstypes.h(864) : see declaration of 'CS_DATAFMT' dbdimp.c(3916) : error C2146: syntax error : missing ';' before identifier 'datafmt' dbdimp.c(3916) : error C2065: 'datafmt' : undeclared identifier dbdimp.c(3918) : warning C4133: 'function' : incompatible types - from 'int *' to 'CS_DATAFMT *' dbdimp.c(3921) : error C2224: left of '.maxlength' must have struct/union type dbdimp.c(3926) : warning C4018: '< ' : signed/unsigned mismatch dbdimp.c(4146) : warning C4244: 'function' : conversion from 'CS_BIGINT' to 'const NV', possible loss of data dbdimp.c(4151) : warning C4244: 'function' : conversion from 'CS_UBIGINT' to 'const NV', possible loss of data dbdimp.c(5124) : warning C4244: '=' : conversion from 'long' to 'CS_BINARY', possible loss of data NMAKE : fatal error U1077: '"C:\Program Files\Microsoft Visual Studio 8\VC\BIN\cl.EXE"' : return code '0x2' Stop.[/text] The nmake will now complete with many warnings. I've started on working up a patch for the DBD::Sybase maintainer, Michael Peppler. Tar and gzip the blib directory and call it DBD-Sybase-1.14.tar.gz. Put it in a directory like so: "MSWin32-x64-multi-thread-5.16\DBD-Sybase-1.14.tar.gz" [text]nmake ppd[/text] Now, you will have a file called DBD-Sybase.ppd consisting of: [xml num=1]
DBI driver for Sybase datasources
Michael Peppler (mpeppler@peppler.org)




[/xml]

If you want to build multiple architectures, you will need to build the Module on the appropriate platform. e.g. Windows 7 64bit. I haven’t had much luck with cross-compilers with ActiveState Perl. YMMV. Once you have the second tar ball, simply add it to your PPD file:

<softpkg NAME="DBD-Sybase" VERSION="1.14">
    <abstract>DBI driver for Sybase datasources</abstract>
    <author>Michael Peppler (mpeppler@peppler.org)</author>
    <implementation>
        <architecture NAME="MSWin32-x64-multi-thread-5.16"></architecture>
        <codebase HREF="MSWin32-x64-multi-thread-5.16\DBD-Sybase-1.14.tar.gz"></codebase>
    </implementation>
    <implementation>
        <architecture NAME="MSWin32-x86-multi-thread-5.16"></architecture>
        <codebase HREF="MSWin32-x86-multi-thread-5.16\DBD-Sybase-1.14.tar.gz"></codebase>
    </implementation>
</softpkg>

I typically zip up the PPD file and the two directories listed in the PPD and distribute that. How you do it is entirely up to you.

Oh, if you want DBD::Sybase on Windows to connect to Microsoft SQL Server, build with FreeTDS.

Share Button

HowTo: Comparing two tables and returning the # of rows that match FAST

The most intuitive way to write a comparison of two tables and spit out the # of rows that are the same involves a left join:

select 'Investment' as TableName, count(*) as RowCount
from Investment_A a, Investment_B b
where
  a.col1 = b.col1
  AND a.col2 = b.col2
  AND a.col3 = b.col3
  AND a.col4 = b.col4

This returns the correct answer but is very slow. Is there a better way? Of course!

select 'Investment' as TableName, count(*) as RowCount
from
(
   select 1 as num
   FROM (
      select * from Investment_A
      UNION ALL
      select * from Investment_B
   ) tmp
   GROUP BY col1, col2, col3, col4
   HAVING COUNT(*) > 1
) tmp2

By pushing the comparison off into the GROUP BY, we leverage the DBMS engine far more efficiently. There are two drawbacks:

  1. Readability of the SQL code
  2. Far more temporary storage is used for the GROUP BY. There is a real risk of running out of temporary storage if the tables are large.
Share Button

Wanted: Microsoft SQL Server DBA (Chicago, IL)

Position Responsibilities

Provides DBA services for Production, test, and development databases
Administers and maintains the production, test, and development databases.
Performs SQL code releases
Reviews application designs for compliance with production acceptance requirements.
Complies with IT policies and procedures, especially those for quality and productivity standards
Complies with Information Security policies and procedures. Verifies deliverables meet Information Security requirements.
Participates in special projects and performs other related duties as assigned.
Operations are 24 x 7. May be required to be on call, work beyond normal business hours

Position Requirements

B.S. in computer science
2 to 3 years experience
Strong written and oral communication skills.
Strong analysis and problem solving skills.
Working knowledge of the following DBA practices and concepts: database backup & recovery, database performance & tuning, database monitoring, and maintenance.
Working knowledge of Visual Source Safe
Strong knowledge of database design and administration
Stored procedure and view development and troubleshooting
Database size management (includes volume/disk mgmt., file and filegroup allocations.)
Basic understanding of Microsoft Clustering technology
Proficient with Microsoft Office Suite
Entry level knowledge of networking
Entry level skill set of database performance tuning/indexing
SSRS, SSIS, and scripting experience a plus.

Interested? (say I referred you on the application form)

Share Button

Introducing QweryBuilder, a Developer GUI for Sybase ASE, SQL Anywhere, Microsoft SQL Server and Oracle 10g/11g

qwerybuilder_reflectedimageQweryBuilder uses innovative ideas to provide developers with the ability to easily insert, extract and modify data from a variety of databases.

The goal of QweryBuilder is to increase a database developer’s productivity. It contains many time saving features.

SQL Editor

QweryBuilder - SQL Editor
QweryBuilder - SQL Editor
  • Script auto complete
  • Custom auto complete lists
  • Code Templates
  • Display column list for tables and views in editor
  • Display procedure parameters in editor
  • Keyboard shortcut to open procedures
  • Generate and insert new GUIDs
  • Syntax folding
  • Auto indenting

Criteria Query

QweryBuilder - Criteria Query
QweryBuilder - Criteria Query
  • Form view criteria screen for easy data retrieval
  • Updateable result set
    • Insert nulls, GUID’s, and computed values into results
    • Insert, delete and update data rows
    • Generate insert statements from results
    • Create graphs from result data

Graphic Query

QweryBuilder - Graphic Query
QweryBuilder - Graphic Query
  • Create queries graphically
  • Cross table querying without writing SQL

Other Features

  • Support for Microsoft SQL Server, Sybase ASE, SQL Anywhere and Oracle
  • DDL Browser
  • Database Search
  • Visual Difference
  • Code Formatter
  • Getting Started Window
    • Displays last used connections, files and database objects
    • Easily navigate to the last thing you were working on
  • Clipboard Saver
  • File Browser
  • Object Browser
Share Button

Anybody running VMware’s VMotion in production?

Don’t know what VMware VMotion is? VMotion allows you to easily move one virtual machine to another VMware ESX host that is connected to the same storage mechanism (presumably SAN) with no downtime. Looks promising 🙂

Image courtesy of VMware
Image courtesy of VMware

Dell has a demonstration using Microsoft SQL Server where they move the live SQL Server from one blade to another with no outage:

Share Button

Native support of SQLite in Sybase PowerBuilder and PowerDesigner?

What I would love to see is native support for SQLite. http://www.sqlite.org

It is the most installed embedded database on the planet hands down.  Don’t believe me?  You know that Firefox web browser, Thunderbird newsreader, most Adobe products, Miro, etc all have it embedded?  – reference http://www.sqlite.org/mostdeployed.html

We use it extensively at work as:

  1. staging for mass data imports/exports/conversions
  2. local application ‘cache’ for large data sets
  3. projects that don’t require all the features of Sybase ASE (or Oracle for that matter)

One of the best features is that the database itself in platform independent… copy the db on to AIX from your Windows box … then on to your old Amiga … then on to your windows mobile device.  Getting the point?  🙂

I really wish Sybase would make it so that the Sybase ASE databases were truly platform and character set/sort order independent… but that is in another dream 😉

Not a blurb in the PowerDesigner/PowerBuilder manuals or anything

Share Button