Finding Sybase ASE database page size

1) t-sql



select @@pagesize, @@maxpagesize
2) t-sql

select pagesize("master..sysobjects")




3) t-sql (works across all Sybase version)

select

v.low as 'pagesize'
from master.dbo.spt_values v
where v.number = 1
and v.type = "E"

4) check database log file:


 grep "pagesize" logfile.txt

No default configure value for Sybase?

One easy to know what is your sybase database server instance's configuration value which is NOT same as the default is to run this query:
sp_configure 'display'

string splitter with t-sql (sql server and sybase)

The following function for sql server should give you an idea of string splitting into a table without looping. The idea should work with both sql server and sybase.

CREATE FUNCTION [dbo].[SplitString](@arr AS VARCHAR(8000), @sep AS CHAR(1))

RETURNS TABLE

AS

RETURN

SELECT

(number - 1) - LEN(REPLACE(LEFT(@arr, number - 1), @sep, '')) + 1 AS pos,

LTRIM(RTRIM(SUBSTRING(@arr, number, CHARINDEX(@sep, @arr + @sep, number) - number))) AS element

FROM (select number from master..spt_values where type='p') as Numbers

WHERE number <= LEN(@arr) + 1

AND SUBSTRING(@sep + @arr, number, 1) = @sep;

Reset sa password for MS sql server and Sybase ASE

It may happen that you may not be able  to access some running sql server at all. You may forget to assign administrators to sql server as administrator or you forget sa password.

What you need is to have at least one account with windows administrator' privilege and you can add any windows account access to sql server. Please note that windows authentication is always possible, so you may just assign admin right for sql server with any windows account and reset sa password if necessary.

Before you re-install the sql server, just follow these steps.

Assumption:

•     sql server installation is complete and is sql server service is running correctly.

•     Forgot sa password, your known window account does not have permission to connect to sql server.

•     Have a window account which has admin right, or at least some one can give you one account with this privilege.

Here are the steps to take

•     step 1: start sql server in minimum configuration mode. For example:

            for default instance
        cd  "C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Binn\"
        or for SQL2008 instance
        cd "C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2008\MSSQL\Binn\"
        sqlservr.exe -f (or sqlservr.ex -m)
        Note: make sure you "Run as administer" from DOS!
        Have a window account which has admin right, or at least some one can give you one account with this privilege.

•    step 2: connect to sql server (using the same account which started the sql server from step 1)

        list all sql server running "near you" or "at the windows network"
        sqlcmd -L
        sqlcmd -E -S MSSQLSERVER (or the correct sql server instance name)

•    step 3: add your windows account or build in admin account as sql server administrator

               CREATE LOGIN [BUILTIN\Administrators] FROM WINDOWS WITH DEFAULT_DATABASE=[master]
                GO
                EXEC master..sp_addsrvrolemember @loginame = N'BUILTIN\Administrators', @rolename = N'sysadmin'
                GO

•         step 4: configure all logins and remove logins which are not necessary any more.

P.S.
            Different way to start up sql server manually.
            1) net start:
        -- default instance name/service name for sql server
        -- net stop mssqlserver
        -- net start mssqlserver
      2) using sqlservr.exe
    
      reference:


        /**
        method 1
        http://blog.sqlauthority.com/2009/02/10/sql-server-start-sql-server-instance-in-single-user-mode/
        adding -m; in start parameter
        */

        /* method 2
        (sqlcmd is better than osql)
        find the list of sql server instance
        sqlcmd -L
        -- connect
        sqlcmd -E -S sql_server_instance
        */

                change sa password with t-sql:
        USE [master]
        GO
        ALTER LOGIN [sa] WITH PASSWORD=N''
        GO
        -- this will be removed in future version
        sp_password @old=NULL, @new=N'', @loginame=N'sa'
        go

You may think of change sql server login mode, but you will always have window authentication anyway.
But in case you want to change the security mode after installation is to stop SQL Server and set the appropriate registry key for your installation:

(from MSDN, this seems for sql server 2000)

Default instance:
HKLM\Software\Microsoft\MSSqlserver\MSSqlServer\LoginMode
Named instance:
HKLM\Software\Microsoft\Microsoft SQL Server\Instance Name\MSSQLServer\LoginMode
to 0 or 2 for mixed-mode or 1 for integrated. (Integrated is the default setup for the SQL Server 2000 Data Engine.)

Note The 0 and 2 values have the same functionality. They both set the security to mixed-mode.

Another note, to reset sa password for Sybase ASE, you can start up server with some optional.

http://www.sybaseteam.com/recover-sa-password-sybase-ase-step-by-step-t-933.html

The following is copied from the url above:
Recover sa password in Sybase ASE - Step by Step process

Recover sa password in Sybase ASE - Step by Step process

If the sa password is lost, the step by step process mentioned below can be followed to recover the ‘sa’ password.

Step 1. Shutdown the server - kill the processes at OS level as it is not possible to shutdown the server
Step 2. Make a copy of RUN_ file say. RUN__backup
Step 3. Edit the RUN_ file to include an argument -psa . Including "-psa" to the RUN file will generate a new password for the 'sa' login. After editing , the RUN_ file looks as below.

----------------------------------------------------------------------------------
$cat RUN_TEST_SYBASE15
#!/bin/sh
#
# ASE page size (KB): 2k
# Master device path: /usr/local/sybase15/sybdev/data/master.dat
# Error log path: /usr/local/sybase15/ASE-15_0/install/TEST_SYBASE15.log
# Configuration file path: /usr/local/sybase15/ASE-15_0/TEST_SYBASE15.cfg
# Directory for shared memory files: /usr/local/sybase15/ASE-15_0
# Adaptive Server name: TEST_SYBASE15
#
/usr/local/sybase15/ASE-15_0/bin/dataserver \
-d/usr/local/sybase15/sybdev/data/master.dat \
-e/usr/local/sybase15/ASE-15_0/install/TEST_SYBASE15.log \
-c/usr/local/sybase15/ASE-15_0/TEST_SYBASE15.cfg \
-M/usr/local/sybase15/ASE-15_0 \
-sTEST_SYBASE15 \
-psa \

------------------------------------------------------------------------------------

Step 4. Start the Sybase server using the command startserver.The password will be printed out on the console as shown below( please note that the sa password will not be written into error log) .Look for it on the console.

$startserver -f RUN_TEST_SYBASE15
00:00000:00000:2010/05/05 16:13:55.69 kernel SySAM: Checked out license for 1 ASE_CORE (2009.1231/permanent/19B3 47BC 0B7E 8DC3).
.............................................................
.............................................................
00:00000:00001:2010/05/05 16:14:01.28 server Completed REDO pass for database 'sybsystemprocs'.
00:00000:00001:2010/05/05 16:14:01.28 server Recovery of database 'sybsystemprocs' will undo incomplete nested top actions.
00:00000:00001:2010/05/05 16:14:01.28 server Started recovery checkpoint for database 'sybsystemprocs'.
00:00000:00001:2010/05/05 16:14:01.30 server Completed recovery checkpoint for database 'sybsystemprocs'.
00:00000:00001:2010/05/05 16:14:01.32 server Started filling free space info for database 'sybsystemprocs'.
00:00000:00001:2010/05/05 16:14:01.34 server Completed filling free space info for database 'sybsystemprocs'.
00:00000:00001:2010/05/05 16:14:01.36 server Started cleaning up the default data cache for database 'sybsystemprocs'.
00:00000:00001:2010/05/05 16:14:01.36 server Completed cleaning up the default data cache for database 'sybsystemprocs'.
00:00000:00001:2010/05/05 16:14:01.37 server Checking external objects.
00:00000:00001:2010/05/05 16:14:01.43 server The transaction log in the database 'sybsystemprocs' will use I/O size of 2 Kb.
00:00000:00001:2010/05/05 16:14:01.44 server Database 'sybsystemprocs' is now online.

New SSO password for sa:yjyhzychbtafk8

00:00000:00008:2010/05/05 16:14:01.48 kernel.......................................
00:00000:00008:2010/05/05 16:14:01.48 kernel.......................................
----------------------------------------------------------------------------------------

Step 5. Login as sa user using the above password.
Step 6. Change the sa password using sp_password command.

$isql -Usa -w132 -c -Pyjyhzychbtafk8
1> sp_password yjyhzychbtafk8,qwerty4321
2>
Password correctly set.

(return status = 0)
1>

Step 7. Remove the -psa argument in RUN_ file. / Copy the RUN__backup file to RUN_ file.

Different way to get sql server instance name

--1.
SELECT SERVERPROPERTY('InstanceName')

--2
sp_helpserver

--3
select @@SERVERNAME

--4
SELECT * FROM SYS.SYSSERVERS

--5
SELECT * FROM SYS.SERVERS

--6
select @@servicename

normalization definition

Table 1. Data Normalization Rules.

Level	Rule
First normal form (1NF)	2 conditions: An entity type is in 1NF when  (A)  it contains no repeating groups of data. (This is typically already implied in sql server, unless you have varchar type to hold an array of data, say "blot/screw/ for product field, which represents all products for a specified store. Also xml data type could hold array of data) And  (B) it is not allowed to have duplicated rows. (this can be done easily with a primary key of a table). It has to have a primary key, otherwise it will allow duplicate data, which violates 1NF. Also repeated data comes with mixing up 2 entities into 1 tables, such as AccountOrder type table. or Inventory table where both product and store information is stored in this table.
Second normal form (2NF)	An entity type is in 2NF when it is in 1NF and when all of its non-key attributes are fully dependent on its primary key.  That is, partial dependance (for example, depend on only one of composed primary key field) is not allowed. Typically dimensional tables in OLAP database only normalized to 2ND. However, fact tables are generally normalized to 3NF.
Third normal form (3NF)	An entity type is in 3NF when it is in 2NF and when all of its attributes are directly dependent on the primary key. That is, non-key attributes depend ONLY (Solely) on primary key, nothing else. If Store table contains Product column. Since product does depend on store since it specifies the product is manufactured by that store. However product does not depend ONLY on store. So this is 2NF, not 3NF table. To make a 3NF table, we need to create a new table Product.

Linux bash getopts sample

 

bash getopts example

# adding : at beginning for more user friendly option error handling 
while getopts ":a:b:c" Option
# Initial declaration.
#
do
case $Option in
# w ) CMD=$OPTARG; FILENAME="PIMSLogList.txt"; TARGET="logfiles"; ;;
  a) a=$OPTARG ;;
d ) b=$OPTARG ;;
c ) c=FlogForCInOn ;;
* ) echo "Usage $basename -a A -b B -c arguments"
esac
done
shift $(($OPTIND - 1))
# now deal with the arguments

Find n-th element with t-sql

Here are few methods to get n-th element from a result set (or a table).
1) using sub query
--http://www.sqlteam.com/article/find-nth-maximum-value-in-sql-server

 use master
 go
 with T as
 (
 select
   number
   ,low
   ,high
   ,status
   from master..spt_values
   where type='P'
 )

 select
  *
  from T as A
  where 4=(select count(distinct B.number) from T as B where B.number<A.number)
 go
2) for sql server 2005 and above, rely on row_number function
 use master
 go
 with T as
 (
 select
   number
   ,ROW_NUMBER() over(order by number) as NumberOrder
   ,DENSE_RANK() over(order by number) as NumberRank
   ,low
   ,high
   ,status
   from master..spt_values
   where type='P'
 )
 --select * from T
 --select top 1 * from (select top 5 * from T order by number) X order by number desc
 select
  *
  from T
  where NumberOrder=5
  --where NumberRank=5
 go
3) double flip:
-- first part is identical
--select * from T
--select top 1 * from (select top 5 * from T order by number) X order by number desc