SAP ASE (Sybase) - find the host ID of ASP ASE

Linux:

<SAP FOLDER>\SYSAM-2_0\bin>lmutil lmhostid

lmutil - Copyright (c) 1989-2013 Flexera Software LLC. All Rights Reserved.
The FlexNet host ID of this machine is "000c7a854v87"

Windows:
Run from cmd:
ipconfig /all
--> Physical Address is the Host ID.

Windows IP Configuration

   Physical Address. . . . . . . . . : 00-0C-7A-85-4V-87

SAP ASE (Sybase) - Check the Edition of the license in the error log (after start the server)

Look for "This product is licensed to:"
It comes after the SySAM logs, look there fo find from where it take the license file etc.

You can find the license source by looking for "kernel  SySAM: Using licenses from"

Stop SAP ASE

Use isql to log in to an SAP ASE sa account

1> shutdown 
2> go

SAP ASE (Sybase) - couldn't create kernel region, could not create shared memory

What I tried to do:
Start ASE service.
(Using bat file or start thr service).

Error message:
kernel  kbcreate: couldn't create kernel region.
kernel  kistartup: could not create shared memory

Cause:
The value of ASE parameter "max memory" is larger than the Operating System shared memory.

Optional Solutions:
(There is no one solution to all cases)

1. try to edit the value in the configuration file (<SAPASEFOLDER>/<SERVERNAME>.cfg :
[Physical Memory]
max memory = 

2. Restart to the server.

SAP ASE (Sybase) - configure the number of user connections

-- Change the number:
sp_configure 'number of user connections', 50

-- Display the current setting:
sp_configure 'number of user connections'

Run a script using isql

isql64 -S<SERVERNAME> -Usa -P<PASSWORD> -i <FILEFULLPATH>

Optional:
add "-o output.txt" to save output in a file.

Sybase Database Error: The specified device is not a database device.

What I tried to do
Create a segment for a database.

Error message:
Sybase Database Error: The specified device is not a database device.

Solution:
You probably mention a device that not in use in the database you try to add athe schema to.

SQL SERVER - List of tables with size and rows count

SELECT s.Name AS SchemaName,
t.Name AS TableName,
p.rows AS RowCounts,
CAST(ROUND((SUM(a.used_pages) / 128.00), 2) AS NUMERIC(36, 2)) AS Used_MB,
CAST(ROUND((SUM(a.total_pages) - SUM(a.used_pages)) / 128.00, 2) AS NUMERIC(36, 2)) AS Unused_MB,
CAST(ROUND((SUM(a.total_pages) / 128.00), 2) AS NUMERIC(36, 2)) AS Total_MB
FROM sys.tables t
INNER JOIN sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN sys.allocation_units a ON p.partition_id = a.container_id
INNER JOIN sys.schemas s ON t.schema_id = s.schema_id
GROUP BY t.Name, s.Name, p.Rows
ORDER BY 6 desc
GO

SAP Control Center - This site can’t be reached

Problem:
Can't access to Control Center: "This site can’t be reached".

Solution:
Enable flash:

Chrome-->Settings-->Site Settings-->Flash
1. change Flash definition to "ASk First"
2. Add SCC URL (https://localhost:8283/scc/# etc.) to the allows list.

SAP ASE (Sybase) - View active transactions

In order to view active transactions, run 
sp_transactions

AIX - CPU, memory and IO (disks space) commands

CPU usage for each process:
topas –P

CPU details:
mpstat

Memory details:
vmstat
svmon

Disk IO details:
iostat
topas -D 

telnet is not recognized as an internal or external command, operable program or batch file

Error message when run telnet:
'telnet' is not recognized as an internal or external command, operable program or batch file.

Solution:
turn "Telnet Client" on via "Programs and Features"-->"Installed updates"-->"Turn Windows features on or off".

SAP ASE installation error: Using locale name "C.UTF-8" defined in environment variable LANG

Error message:

The context allocation routine failed when it tried to load localization files!!

One or more following problems may caused the failure

Your sybase home directory is <DIRECTORYNAME>. Check the environment variable SYBASE if it is not the one you want!
Using locale name "C.UTF-8" defined in environment variable LANG
Locale name "C.UTF-8" doesn't exist in your /<SAP FOLDER>/locales/locales.dat file
An error occurred when attempting to allocate localization-related structures.

Solution:

<SAP FOLDER>$ export LANG=

SAP ASE installation error: Command 'isql' not found

Error message:
Command 'isql' not found, but can be installed with:
apt install unixodbc
Please ask your administrator.

Solution:
<SAP FOLDER>$ source  SYBASE.sh

SAP ASE: Connect to the database using SCC - SAP Control Center

SCC - SAP Control Center - a client tool - Web-based administrative console- of SAP to the ASE databases.
SAP Control Center replaces Sybase Central - the client tool when ASE was Sybase.

Read more here:
https://help.sap.com/doc/saphelp_iq1608_iqnfs/16.0.8/en-US/a8/780f2d84f210158b8c895da947e314/frameset.htm

In order to connect to the database using SCC:

* Resource Registration:
1. Resource-->Register - Resource Registration window will be open.
2. Resource type:
- Resource name: enter a name (just a name for you to recognize the server).
- Resource type: ASE server.
3. Connection information:
- ASE host name: the server name or IP.
- ASE port number: port number.
- Character set: -
- Language: -
4. Authentication Information:
- Enter user and password.
5. Options:
- Sign V V.
*. In the first connection the SCC will suggest to change few configuratuins. DO that...

* In order to execute SQL:
1. "Prespective Resources" window, from the server name open "Administration Console"

2. in "Administration Console" --> "Resource Selection"

SAP Replication Server Error: can not connect

Error Message:
ct_connect(): network packet layer: internal net library error: Net-Lib protocol driver call to connect two endpoints failed

Solution:
Start Replication Server (service in Windows, or batch file in Linux).

SAP ASE (Sybase): Add an identity column

In SAP ASE (Sybase) identity column has to be declared as numeric(X,0).

CREATE TABLE TABLENAME
(
id_column_name numeric(5,0) identity, 
...

SAP ASE (Sybase): Collection of monitoring data requires enable monitoring

Error Message while try to run queries for SAP ASE server:
Collection of monitoring data for <OBJECT> requires that the 'enable monitoring' configuration option(s) be enabled. 

Solution:
sp_configure 'enable monitoring', 1

Test Open Ports with Telnet

Open cmd and run:
telnet <IP_ADDRESS> <PORT_NUMBER>

Example:
telnet 30.125.40.120 5000

* If telnet doesn't work, turn "Telnet Client" on via "Programs and Features"-->"Installed updates"-->"Turn Windows features on or off".

SAP ASE (Sybase) ase 15.7 server invocation of this java application

What I tried to do?
Install SAP ASE 15.7 server, run the setup file.

Error:
This Application has Unexpectedly Quit
Invocation of this Java application has cause an Invocation Target Exception. this application will now exit

Cause:
Not fit SAP ASE release to the OS.
Look at SAP docs if the ASE release can be installed on the OS.

Start Sybase server

startserver -f RUN_servername

If you've got this error message:
bash: startserver: command not found...

Find where it is placed and run:
. SYBASE.sh ( the file with the Sybase Product Environment variables)
After it run again startserver from its folder.

SAP ASE (sybase) - Move a table to another Segment

sp_placeobject SEGMENTNAME , 'TABLENAME'

SAP ASE (sybase) - Create Database

A device has to be created.
How to create a device:
https://copypastenet.blogspot.com/2019/12/sap-ase-sybase-create-device.html


-- Create Database
create database DATABASENAME
    on DEVICENAME='100M'      -- data device
    log on LOGDEVICENAME='100M' -- log device, if not declared - log will be in the same device as the data
GO

For more options in the creation:
http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.infocenter.dc36272.1600/doc/html/san1393050910896.html

SAP ASE (sybase) - Create Segment

sp_addsegment SEGMENTNAME, DATABASENAME, DEVICENAME

SAP ASE (sybase) - Create a Device

-- Create a device - disk init
disk init 
  name="DeviceName", 
  physname="/sybvol01/sap16/data/DeviceName1.dat", 
  size="250M"
GO

For more parameters of disk init:

http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.infocenter.dc36272.1550/html/commands/X64061.htm

Connect to isql

isql Uusername Ppassword [Sserver]

(U , P , S - as is and then replace to the user, password, ...)
( Pay attention: names are case-sensitive )

Unix/Linux zip commands

zip file: 
zip FILENAME.ZIP

unzip file: 
unzip FILENAME.ZIP

unzip to a folder: 
unzip FILENAME.ZIP -d FOLDERNAME

instsall zip:
sudo apt install zip

Unix/Linux files commands

remove a file
rm
(rm * - remove all files in the folder)
(-f - force deletion)

find a file in a folder including sub-folders by it's name:
find -name FILENAME
find a file in a folder including sub-folders by part of it's name:
find -name FILEN*

view text of a file
more FILENAME

• Exit by pressing q key

edit a file
vi FILENAME

• To save a file: press Esc key, type :w, Enter
• To save a file and quit: press Esc key, type :x, Enter

grant privilege to a user
chown -R USERNAME: FOLDERNAME
(-R - to recursively operate on all files and directories under the given directory)

Unix/Linux System and enviroment commands

show enviroment parameters
env
show enviroment parameters results match a specified pattern
env | grep STRING

add parameter to env or replace exists value:
export PARAM="VALUE"
concantanete value to exists parameter in env:
export PARAM="VALUE":$PARAM

available and used disk space usage
df
(df -h - more human display)

Unix/Linux basic commands

where I am
pwd

go to FOLDERNAME folder
cd FOLDERNAME

go to parent folder
cd ..

display what is in the folder
ls
(-l all files)
(- la all include hidden)

create a folder
mkdir FOLDERNAME

display an user manual for a command
man COMMAND

switch user
su

run a command like admin (= "as administrator" in windows)
sudo COMMAND ...

desplay results that match a specified pattern
COMMAND | grep STRING

show commands history
history

Get database schema changes history (by T-SQL query)

DECLARE @db_name TABLE

(

       db_name_ID int identity,

       DBName sysname

);

DECLARE @objects TABLE

(

       db_name sysname,

       ob_name sysname,

       type nvarchar(60),

       date datetime,

       s_name sysname

);

DECLARE @cnt int;

DECLARE @DBName sysname;

INSERT INTO @db_name

       (DBName)

       VALUES ('Your_Database_Name')

       -- OR SELECT name FROM sys.databases WHERE database_id <> 2;

SELECT @cnt = COUNT(*) FROM @db_name;

WHILE @cnt > 0

BEGIN

       SELECT @DBName = DBName FROM @db_name WHERE db_name_ID = @cnt;

      

       INSERT INTO @objects

              exec('use ['+@DBName+']; SELECT '''+@DBName+''' as db_name,o.name as object_name,o.type_desc, o.modify_date, s.name as schema_name from sys.all_objects o left outer join sys.schemas s

                             on (o.schema_id = s.schema_id) where modify_date > ( GETDATE() -3000);');

      

       SET @cnt = @cnt-1;

END

SELECT *

FROM @objects

       --where ob_name = 'Your_Object_Name'

ORDER BY date desc, db_name, s_name, ob_name;

SSIS, TVP: An OLE DB record is available. Source: "Microsoft SQL Server Native Client 11.0" Hresult: 0x80040E21 Description: "Invalid character value for cast specification"

Error:

An OLE DB record is available. Source: "Microsoft SQL Server Native Client 11.0" Hresult: 0x80040E21 Description: "Invalid character value for cast specification".

Cause:

Not fit parameter declaration.

But:

If your all parameters were declared well, but you still have this error, and one of the parameter is a TVP (Table Variable Parameter):

The TVP is the cause...

I tried to pass a SSIS Object to TVP in the stored procedure, and I've got this error.

Solution:

I pass the data to a temp table in the destination DB, and took it from there to the SP.

SSIS: resize container to proportional size

Problem:

Anyone who worked with SSIS gets crazy from the tries to resize the containers to proportional size…

Solution:

SSIS: copy package between projects

In order to quick copy a package from SSIS project to another:

1. Copy the package file from Windows Explorer.
2. Paste it on the project node in the Visual Studio.

SSIS Excel Connection Manager 64-bit error

Error message:

[Connection manager "XXX Excel Connection Manager"] Error: The requested OLE DB provider Microsoft.ACE.OLEDB.12.0 is not registered. If the 64-bit driver is not installed, run the package in 32-bit mode. Error code: 0x00000000.

An OLE DB record is available.  Source: "Microsoft OLE DB Service Components"  Hresult: 0x80040154  Description: "Class not registered".

Solution:

Right click on the project name à properties

Configuration Properties à Debugging: Set Run64BitRuntime to False.

SQL Server String: Get File Name from Full File path

DECLARE @FullPath NVARCHAR(500) = N'D:\Itai\Tasks\MyFile.csv'

SELECT LEFT(@FullPath,LEN(@FullPath) - charindex('\',reverse(@FullPath),1) + 1) AS OnlyPath,

       RIGHT(@FullPath, CHARINDEX('\', REVERSE(@FullPath)) -1) AS OnlyFileName

SQL Server: Run a query without execute it

In order to run a query without execute it – only compile and/or check and/or validate it:

SET NOEXEC ON

How to enable a database user?

Problem:

Giving permissions to a database user in not take effect.

Posible couse:

The user is "disabled". the user is showing up as disabled  - with a down reddish arrow.

Solution:

USE [DataBaseName]

GO

GRANT CONNECT TO [Username]

GO

SQL table "Inside Order" - Identity VS CLUSTERED INDEX

Q: What will be the result order of a select (without ORDER BY) from a table - with/without Identity and CLUSTERED INDEX?

And when we will add Identity and/or CLUSTERED INDEX - how will this affect?

A: Follow the next selects:

create table dbo.NoID

(      someSTR nvarchar(50),

       someDate datetime

) ON [PRIMARY];

insert into NoID (someSTR, someDate) values ('aaa', GETDATE())

insert into NoID (someSTR, someDate) values ('bbb', GETDATE())

insert into NoID (someSTR, someDate) values ('ccc', GETDATE())

insert into NoID (someSTR, someDate) values ('zzz', GETDATE())

insert into NoID (someSTR, someDate) values ('ddd', GETDATE())

insert into NoID (someSTR, someDate) values ('eee', '2017-01-01 00:00:00')

select * from NoID

alter table dbo.NoID add ID int identity(1,1) not null

select * from NoID

create table dbo.NoID_PKClusteredNotOrderedByTime

(

       someSTR nvarchar(50),

       someDate datetime,

       CONSTRAINT [PK_NoID_PKClusteredNotOrderedByTime] PRIMARY KEY CLUSTERED

       (

              [someSTR] ASC

       ) ON [PRIMARY]

) ON [PRIMARY];

insert into NoID_PKClusteredNotOrderedByTime (someSTR, someDate) values ('aaa', GETDATE())

insert into NoID_PKClusteredNotOrderedByTime (someSTR, someDate) values ('bbb', GETDATE())

insert into NoID_PKClusteredNotOrderedByTime (someSTR, someDate) values ('ccc', GETDATE())

insert into NoID_PKClusteredNotOrderedByTime (someSTR, someDate) values ('zzz', GETDATE())

insert into NoID_PKClusteredNotOrderedByTime (someSTR, someDate) values ('ddd', GETDATE())

insert into NoID_PKClusteredNotOrderedByTime (someSTR, someDate) values ('eee', '2017-01-01 00:00:00')

select * from NoID_PKClusteredNotOrderedByTime

alter table dbo.NoID_PKClusteredNotOrderedByTime add ID int identity(1,1) not null

select * from NoID_PKClusteredNotOrderedByTime

create table dbo.NoID_ClusteredNotOrderedByTime

(

       someSTR nvarchar(50),

       someDate datetime

) ON [PRIMARY];

insert into NoID_ClusteredNotOrderedByTime (someSTR, someDate) values ('aaa', GETDATE())

insert into NoID_ClusteredNotOrderedByTime (someSTR, someDate) values ('bbb', GETDATE())

insert into NoID_ClusteredNotOrderedByTime (someSTR, someDate) values ('ccc', GETDATE())

insert into NoID_ClusteredNotOrderedByTime (someSTR, someDate) values ('zzz', GETDATE())

insert into NoID_ClusteredNotOrderedByTime (someSTR, someDate) values ('ddd', GETDATE())

insert into NoID_ClusteredNotOrderedByTime (someSTR, someDate) values ('eee', '2017-01-01 00:00:00')

select * from NoID_ClusteredNotOrderedByTime

CREATE CLUSTERED INDEX [ix_NoID_ClusteredNotOrderedByTime] ON dbo.NoID_ClusteredNotOrderedByTime

(

       [someSTR] asc

) ON [PRIMARY]

GO

select * from NoID_ClusteredNotOrderedByTime

alter table NoID_ClusteredNotOrderedByTime add ID int identity(1,1) not null

select * from NoID_ClusteredNotOrderedByTime

drop INDEX [ix_NoID_ClusteredNotOrderedByTime]  ON dbo.NoID_ClusteredNotOrderedByTime

alter table NoID_ClusteredNotOrderedByTime drop column ID

alter table NoID_ClusteredNotOrderedByTime add ID int identity(1,1) not null

select * from NoID_ClusteredNotOrderedByTime

create table dbo.NoID_ClusteredNotOrderedByTimeIdentityBeforeCI

(

       someSTR nvarchar(50),

       someDate datetime

) ON [PRIMARY];

insert into NoID_ClusteredNotOrderedByTimeIdentityBeforeCI (someSTR, someDate) values ('aaa', GETDATE())

insert into NoID_ClusteredNotOrderedByTimeIdentityBeforeCI (someSTR, someDate) values ('bbb', GETDATE())

insert into NoID_ClusteredNotOrderedByTimeIdentityBeforeCI (someSTR, someDate) values ('ccc', GETDATE())

insert into NoID_ClusteredNotOrderedByTimeIdentityBeforeCI (someSTR, someDate) values ('zzz', GETDATE())

insert into NoID_ClusteredNotOrderedByTimeIdentityBeforeCI (someSTR, someDate) values ('ddd', GETDATE())

insert into NoID_ClusteredNotOrderedByTimeIdentityBeforeCI (someSTR, someDate) values ('eee', '2017-01-01 00:00:00')

select * from NoID_ClusteredNotOrderedByTimeIdentityBeforeCI

alter table NoID_ClusteredNotOrderedByTimeIdentityBeforeCI add ID int identity(1,1) not null

select * from NoID_ClusteredNotOrderedByTimeIdentityBeforeCI

CREATE CLUSTERED INDEX [ix_NoID_ClusteredNotOrderedByTimeIdentityBeforeCI] ON dbo.NoID_ClusteredNotOrderedByTimeIdentityBeforeCI

(

       [someSTR] asc

) ON [PRIMARY]

GO

select * from NoID_ClusteredNotOrderedByTimeIdentityBeforeCI

/*

drop table dbo.NoID

drop table dbo.NoID_ClusteredNotOrderedByTime

drop table dbo.NoID_PKClusteredNotOrderedByTime

drop table NoID_ClusteredNotOrderedByTimeIdentityBeforeCI

*/