What I did?
Create a device.
Error message:
Cannot create device 'DEVICENAME': no available virtual device numbers (vdevnos)
Solution:
exec sp_configure 'number of devices', 20
-- replace 20 with a number that is bigger than what it set now.
Cause:
There is a limit of maximum number of devices - you probably get to it.
Check the number of devices:
exec sp_configure 'number of devices'
See the devices in the server:
select * from master..sysdevices
cp <FILENAME> <path>/<NEWFILENAME>
For ASE 15.7:
alter login sa with password 'OLDPASSWORD' modify password 'NEWPASSWORD'
For other ASE:
sp_password 'OLDPASSWORD','NEWPASSWORD' ,sa
If you forgot the password and can't login:
1. go to <SAPFOLDER>/ASE-XX_X/install/
2. Edit the RUN_SID file, add new line in the end of it with: (p=password, sa=sa)
-psa
3. reset server. if you don't have a login to do that - restart the server machine.
4. start SAP ASE:
<SAPFOLDER>/ASE-XX_X/install$ ./startserver -f RUN_SERVERNAME
5. while executing the startserver, a new password will be printed on the terminal screen:
New SSO password for sa:ldxsqzfammlsw2
6. login as sa with the new password
7. change password
After running setup.bin for ASE server (I've got it in 15.7 isntallation), error message was raised:
$ ./setup.bin
Preparing to install...
Extracting the JRE from the installer archive...
Unpacking the JRE...
Extracting the installation resources from the installer archive...
Configuring the installer for this system's environment...
Launching installer...
./setup.bin: 2506: exec: /tmp/install.dir.48193/Linux/resource/jre/bin/java: not found
Cause:
This error occurs because the installer is a 32-bit binary and this casue problems on a 64-bit server.
Solution:
sudo apt-get install lib32ncurses5
After that - rerun setup and it will work.
dbcc logprint('itai test errorlog')
-- will be written with severity=0
or:
sp_addmessage 20002, 'itai test sp_addmessage with_log'
raiserror 20002
-- will be written with severity=0
or:
sp_addmessage 20002, 'itai test sp_addmessage with_log', @with_log=TRUE
raiserror 20002
-- will be written with severity=16
or with parameters:
sp_addmessage 20003, 'itai %1! sp_addmessage with parameters', @with_log=TRUE
raiserror 20003, 'parameter'
-- will be written with severity=16
Windows:
sql.ini in <SAP_ASE_FOLDER>\ini
Linux:
<SAP_ASE_FOLDER>/interfaces
Looks like:
Linux:
MYSERVER
master tcp ether MyServer 5000
query tcp ether MyServer 5000
MYSERVER_BS
master tcp ether MyServer 5001
query tcp ether MyServer 5001
...
Windows:
[MYSERVER]
master=TCP,137.116.163.160,5000
master=TCP,137.116.163.160,5000
[MYSERVER_RS]
master=TCP,13.70.20.172,11753
query=TCP,13.70.20.172,11753
...
. SYBASE.sh
(instead of
source SYBASE.sh
)
Error message:
The transaction log in database sales is almost full. Your transaction is being suspended until space is made available in the log.
Will be repuduced whan trying to work with a table in the database.
Cause:
Log space is full.
Check it using:
sp_helpdb <DBNAME>
sp_helpdevice <DEVICENAME>
Solution:
use master
go
disk resize name = "<DEVICENAME>", size = "500M"
go
alter database sales log on <DEVICENAME> = "500M"
go
SAP ASE Installation steps:
1. Download SAP ASE from SAP site
https://support.sap.com/en/my-support/software-downloads.html
2. Create an user for sybase (DBAdmin)
create a user for sybase (DBAdmin)
create a folder for sybase and give privilage to the user.
$ sudo mkdir <SYBASEFOLDEER>
$ sudo chown -R DBAdmin: <SYBASEFOLDEER>
3. Copy the install file to the linux server (zip file).
4. Extract the ZIP file
regular unzip: unzip 51047759.ZIP
unzip to a folder: unzip 51047759.ZIP -d sybase51047888
( If required: zip installation in the server: sudo apt install zip ).
5. Check the share memory for the host
Check: sudo ipcs -lm
Set:
$ logout
root@SERVER# vi /etc/sysctl.conf
root@SERVER# sysctl -p
kernel.shmmax = 4294967296
6. Check that there is no running Firewall
# systemctl status firewalld
7. Go to the SAP ASE folder and run setup (using the user that was created in step 2).
./setup.bin
During the installation you'll need to set some definitions and choose which components to install.
8. cat SYBASE.sh >> ~/.profile
cd <SAPASEFOLDER>
cat SYBASE.sh >> ~/.profile
9.0 Checks and settings:
export LANG=
source SYBASE.sh
* check that sybase process is up:
ps -ef | grep -i syb
* check that ASE works:
isql64 -S SERVERNAME -Uusername -Ppassword
1> select @@version
2> go
-------------------------------------------------------------------------------
Adaptive Server Enterprise/16.0/EBF 22383 SMP/P/x86_64/Enterprise Linux/aseceph
eus/3530/64-bit/FBO/Sun Feb 16 06:09:40 2014
(1 row affected)
There is no cross join in SAP ASE (Sybase).
select *
from FirstTable
cross join LongRunTB
--> Incorrect syntax near 'LongRunTB'.
In order to perform "cross join":
select *
from FirstTable, LongRunTB
--> works
USE [DATABASENAME]
GO
-- Quick script
select * from sysobjects where type = 'RI'
-- Detailed script
select
tab.name "table name",
fko.name "Foreign key name",
par.name "Referenced table name",
-- fk1.name "FK column name",
-- pk1.name "Ref column name",
*
from sysobjects tab
JOIN sysconstraints con on tab.id = con.tableid
JOIN sysobjects fko on con.constrid = fko.id
JOIN sysreferences ref on con.constrid = ref.constrid
JOIN sysobjects par on par.id = ref.reftabid
-- ---- 1. Column
-- left join syscolumns fk1 on ref.fokey1 = fk1.colid and ref.tableid = fk1.id
-- left joinsyscolumns pk1 on ref.refkey1 = pk1.colid and ref.reftabid = pk1.id
-- etc. to more columns
where tab.type = 'U'
and fko.type = 'RI'
--and tab.name = 'TABLENAME'
IF NOT EXISTS (select * from sysobjects where name = 'FK_NAME' and type = 'RI')
ALTER TABLE [dbo].[TABLENAME] ADD CONSTRAINT FK_NAME FOREIGN KEY ([COLUMNNAME])
REFERENCES [dbo].[REF_TABLENAME] ([REF_COLUMNNAME])
GO
- Create the license file in SAP site. (You will need to enter the server HostID, name, IP).
- Save the license file in the server on : <SAP FOLDER>\SYSAM-2_0\licenses
- Edit properties file (<SAPFOLDER>/ASE-XX-X/<SERVERNAME>.properties: Set PE and LT (for enterprise: PE=EE, LT=CP).
- Restart SAP ASE database server (from services in windows or by ASE commands - stop and start)
Checking that the server is Enterprise (or other) now:
- Check the log after restart - it write in the begining which version is uploaded.
- exec sp_lmconfig
- Check parameters 'edition' and 'license type' and properties PE (EE) and LT (CP).
