Menu
  • HOME
  • TAGS

How to make views out of partitions in Sybase

Tag: sybase,sybase-ase

I am using Sybase 12.5.3, have a table with 12 million rows without any identity/id column. I want to parallely bcp out the data from table to 12 different files.

  • Found that this can only be done if I make views on the table and then run bcp parallely (Reference: link-February 2012 question)
  • My table has partitions also and I have partition ids only (Not partition names)
  • Is there any way I cam make 12 different views of 1 million rows each using either the partitions or using some counterpart of rownum of Oracle in sybase

Help will be really appreciated !!

Best How To :

To bcp out of the partitions, you can reference the partition name or partition number.

bcp mydb..bigtable:1 out file1 -Pmypassword -c &
bcp mydb..bigtable:2 out file2 -Pmypassword -c &

This would create a character (plaintext) output from partitions 1 & 2 of bigtable

Though much of the documentation concentrates on importing data, the systax for exporting is typically very close.

Alternatively, you can create views based on the values in the tables. Assuming there is a column that holds some sort of range (or catagorical) value that can be used to divide the data, you can use something like this:

create view mytable_VIEW_1 as
select * from bigtable
where myColumn < someValue1

create view mytable_VIEW_2 as
select * from bigtable
where myColumn between someValue2 and someValue3

Once your views are created, you can easily bcp out from them.

Bind font symbol from database in XAML

wpf,xaml,sybase

I've fixed the issue by converting the icon in my ViewModel using icon.Icon = System.Text.RegularExpressions.Regex.Unescape(icon.Icon); This converts the icon (stored as a string in the database) to a Unicode character....

Where is PHP's sybase_query Implemented?

php,sybase,php-extension

The source code can be found here on GitHub. This is the maintainer's fork.

Datawindow query is not respecting ORDER BY clause

sql,sql-order-by,sybase,powerbuilder,datawindow

Follow the step.. First give alias for the columns inside select statements..since its union the alias should match in all queries. put an outer select with the alias names and do orderby using the alias of the column u needed. **Keep in mind that order by will effect the execution...

Find if the local temporary table exists in sql anywhere and use it

sql,sybase,sqlanywhere

I'm not sure what version of Sybase you have but this works in Sybase 11 so I can imagine it will work in any version up too: Begin Create local Temporary table TEMP_TABLE (column1 int); //Create temp table // any other code needed to be executed if table did not...

Sybase and jdbc- Proc returns select statement - records not able to get

java,stored-procedures,jdbc,sybase

Your stored procedure doesn't have an out parameter. It produces a ResultSet instead. So you should remove the out parameter definition in your call: String query = "{call my_proc}"; try (CallableStatement stmt = conn.prepareCall(query)) { boolean results = stmt.execute(); while (true) { if (results) { // results true means: result...

How to produce an XML output file from a stored procedure in Sybase?

xml,stored-procedures,sybase

I'm assuming we're looking at Sybase ASE here. You can turn a SQL query result into an XML document by adding 'for xml' at the end (select a,b,c from t for xml); this is the simplest, there are more advanced options to do this too. Th resulting XML document cannot...

What is the locking schema of sybase used for?

database,sybase

The locking scheme can be set a couple different ways. A default can be set at the server level, and all tables created will use the default. If the default is changed, the tables will not convert to the new locking scheme automatically, as far as I know. You typically...

Can an updated with nested select be considered atomic in Sybase?

sql,sybase,atomicity

To guarantee that no such overlaps occur, yo should: (i) put BEGIN TRANSACTION - COMMIT around the statement (ii) put the HOLDLOCK keyword directly behind 'tableX' (or run the whole statement at isolation level 3).

Exit from stored procedure in sybase

sybase-ase

You can use return command as below -- validation section IF (len(zip)<>5 OR LEN(zip)<>9) begin print "The zip must be of 5 or 9 characters" return 1 end IF (len(name)<2) begin print "The name must be of at least 2 characters" return 2 end return 0 -- on the end...

Selecting Data based on a specific condition ,which is the optimal solution using sql statement or stored procedure using cursor in sybase

sql,sql-server,sybase,sybase-ase

You can create a temp table and assign all the account numbers from the first query.Put an identity column to your temp table so that its easy to loop through the table. create table #tmp_account(ID int identity not null,acct_num varchar(100)not null) Now loop through the table using ID as the...

hierarchy display Sybase table data

sql,table,sybase

Disclaimer: it's a example made in (MS-SQL) @Gordon Linoff answer almost did it, just forgot the base case (where Pid is null) just follow this pattern of left joins + unions to cover the base cases for a fixed amount of levels, for a dynamic number os levels you ill...

Avoid errors when attempting to convert to datetime

sql,tsql,sybase

Something like below code should work on sybase SELECT convert(datetime, foo_str) FROM foo_tbl WHERE foo_str like '[A-Z][A-Z][A-Z] [0-1][0-9] [0-2][0-9][0-9][0-9] [0-2][0-9]:[0-5][0-9]:[0-5][0-9].[0-9][0-9][0-9]' if 00:00:00.000 is constant you can do it simpler SELECT convert(datetime, foo_str) FROM foo_tbl WHERE foo_str like '[A-Z][A-Z][A-Z] [0-1][0-9] [0-2][0-9][0-9][0-9] 00:00:00.000' or SELECT convert(datetime, foo_str) FROM foo_tbl WHERE foo_str like...

Using ResultSetMetaData with same column name in 2 tables [duplicate]

java,sql,database,sybase,resultset

If you can't or don't want to specify the columnnames (with aliases), then you will need to retrieve the values by index, not by column label. The ResultSet API doc explicitly says: When a getter method is called with a column name and several columns have the same name, the...

Sum across columns and rows

sql,sql-server,sql-server-2005,sum,sybase-ase

Use cross apply with table valued constructor to unpivot the data then find sum per bookid and item. This will avoid your intermediate step SELECT BookId, item, Sum(quantity) FROM Youratble CROSS apply (VALUES(Quantity1,ItemId1), (Quantity2,ItemId2))cs(quantity, item) GROUP BY BookId, item As mentioned by Mikael Eriksson for sql server 2005 use this...

Microsoft SQL @@cpu_busy replacement for CPU saturation stat

sql,sql-server,sybase

Benjamin Nevarez has the answer for CPU utilization: http://sqlblog.com/blogs/ben_nevarez/archive/2009/07/26/getting-cpu-utilization-data-from-sql-server.aspx It uses Dynamic Mgmt View data from sys.dm_os_ring_buffers where ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR'....

how to find tables used in a view on sybase

sql,view,sybase,sybase-ase

You can use sp_depends system procedure as below exec sp_depends 'view name' ...

What is the disadvantage of using a bulk enabled connection for non bulk operations

sybase-ase

Bulk operations do minimal to no logging in the database. This means that in case of a server failure, transactions could be lost, and there would be fewer recovery options available. So the default is to make things as recoverable as possible with being bulk options being disabled, and letting...

pass a variable to predefined function sybase/sql

sql,sybase,sybase-iq

You can not convert an alphanumeric to a bigint with the convert statement above. So you would need to change the convert to first convert Hex to Int. But then you do not need the convert statement as the following two lines return the same value. So just use the...

Bad System Call (core dumped) on Sybase 12.5 installation (SunOS)

installation,system,call,solaris,sybase-ase

Thank you all for your replies. myaut was right when saying: Many system calls were deprecated in Solaris 11, access seem to be deleted My problem therefore seemed related to a incompatibility between Sybase ASE 12.5.4 and Solaris 11 as this link shows: http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.dc35889_1254/html/installsol/X22779.htm Sybase ASE 12.5.4 is compatible with...

Cannot fetch result from a variable in update query

php,sql,odbc,sybase-ase

I solved it by my own. Should add to my code the following: select @status as status into #isupdate from table; select * from #isupdate; // make it as a new query; ...

How to remove/rename a duplicate column in SQL (not duplicate rows)

sql,sql-server,sybase

You'll have to alias one of the two duplicate columns and explicitly indicate specific columns in the select at least for one of the tables (the one you've used the alias on it's column): SELECT firstTable.PatID as 'pID', firstTable.column2, secondTable.* FROM... Notice, I've still used a wildcard on the secondTable....

SAP equivalent of IsMSShipped (ie, is a system object)

sybase,sybase-ase

Technically, system objects are those with their sysobjects.id < 256. These are mostly only system tables that are predefined and cannot be influenced by the DBA. These have sysobjects.type ='S'. This does not include 'system' views (like sysquerymetrics) and system stored procs, like sp_help etc., these are really created afterwards...

Sybase SQL anywhere 11 Grant/Revoke on column views

sql,views,sybase,grant,sqlanywhere

You can't. From the SQL Anywhere 11 docs: "SELECT permissions on columns cannot be granted for views, only for tables". Disclaimer: I work for SAP in SQL Anywhere engineering....

How to see Table columns in sorted order in Sybase

sql,database,sybase,sybase-ase

select col.name from sysobjects tab inner join syscolumns col on tab.id = col.id where tab.type = 'U' and tab.name = 'TABLE_NAME' order by col.name ...

Python-Sybase on Windows 7 Python 2.7 32bit

python,python-2.7,sybase

Infocenter.sybase Basically the error is due to incorrect/missing configuration information in the \locales\locales.dat file for the target OS as per link above: 'The most common reason for a cs_ctx_alloc failure is a misconfigured system environment. cs_ctx_alloc must read the locales file that specifies the default localization values for the allocated...

Data type in sybase function replace() instead of value

sql,function,sybase,netezza

Replace isn't native in Netezza however it is included in the Netezza SQL Extensions Toolkit Replace Works like the example below: replace('ORIGINAL VALUE','VALUE TO REPLACE','REPLACE WITH THIS') replace(Table1.Col1,' ','') In the second example the function will replace any space with nothing....

import sybase data to sql server when any change make in table

sql-server,sybase

I don't see any straight forward solution here. I would use following steps to deal with that issue: Create table with unique key of source_table: Create table mod_date ( key int unique, modified_date datetime ) Create insert/update trigger for source_table that will be inserting/updating modified_date table. When selecting data from...

Get System Error Message In Sybase

sybase-ase

I don't know how to take specify error message - I think it's not possible. Maybe below query will cover your needs. It return and pattern message for example Must declare variable '%.*s'. insetad of Must declare variable 'fake variable'. SELECT description from master..sysmessages where error = @@error @@error variable...

how to get sybase table column name and its datatype and order by

database,sybase

To extract types I am using such query: SELECT syscolumns.name, systypes.name FROM sysobjects JOIN syscolumns ON sysobjects.id = syscolumns.id JOIN systypes ON systypes.type = syscolumns.type AND systypes.usertype = syscolumns.usertype WHERE sysobjects.name LIKE 'my_table' ...

add a getdate() default value while adding a column

sybase,sybase-ase

I solved my question by adding the column to null alter table tab1 add col1 datetime default getdate() null then I modify it to not null...

Strange sybase behavior around daylight savings time (DST)

sybase-ase,dst

Aqua Data Studio is written in Java. The problem you are having has to do with the fact that Java is aware of timezones and databases don't have a concept of timezone when they store date and times. When the time comes back from the database, the database's JDBC driver...

Sybase for update SQL causing deadlock

deadlock,sybase-ase

This is likely due to Table/All Pages locking level set for the table, which means that when a transaction starts, the process acquires locks for the whole table. Your first process and second process are trying to acquire locks on the same resources, and that's causing the deadlock. To change...

Remove duplicate rows between two different columns

sql,sybase

Your sample query uses not exists, so let's continue down that path. The logic is that you want all rows were player_nbr < partner_nbr. Then you want rows where player_nbr > partner_nbr is true but that there is other row with player_nbr < partner_nbr. The following is the logic in...

Sybase Sql query not returning error

sql,null,sybase,isnull

It seems that your variables are not defined during execution of this query. I did small experiment on my side : CREATE TABLE #temp ( a INT NULL, b INT NULL ) INSERT #temp SELECT 1, NULL UNION SELECT 2, 2 DECLARE @b INt SELECT @b = 2 SELECT *...

Unbind all objects of a rule transact sql

sql,sql-server,tsql,sybase-ase

First you need to see which objects are bounded to the rule , after that to unbind the rule from a table you simply do this: sp_unbindrule table, null, "all"...

Calculate working shift giving current datetime in sybase

sql,sybase,sybase-ase

I have no sybase here, then cannot try it. I think what you need is datepart function. This function only gets the hours, or minutes from the datetime value, then you will be able to easily check without thinking in days. I did the following changes: I changed the code...

Get info about foreign key on delete action

sql,foreign-keys,constraints,sybase,sybase-ase

Sybase ASE 15 does not support on cascade DDL so none of your foreign keys will have a cascade option. If you want the delete or update on cascade functionality you must implement a trigger.

How to tell if a view has been created WITH CHECK OPTION in Sybase ASE

sql,sybase,sybase-ase

Looks like this was a bug in Sybase ASE from 12.5 up to 16.0, but may be released in certain service packs (16.0 SP01, 15.7 SP134 - not yet confirmed). http://scn.sap.com/thread/3713912 The solution is to set some switches (200 = print tree before optimization, 3604 = print output to client),...

How to alter Image column on Sybase to NOT NULL

sybase

Text/image datatypes are very different internally from the other datatypes due to the way they are stored. Therefore, it is not a surprise that operations that work on an INT column do not work on a text/image column. The documentation is not terribly clear on this point, but implicitly it...

SQL Conditional sum and grouping

sql,grouping,sybase,conditional-statements

How about this: select patientid, admissionid, datediff(day, max(case when Admission_Event_Type = '(formal) Separation ' then startdate end), max(case when Admission_Event_Type = '(formal) Admission ' then enddate end) ) as total_length from data group by patientid, admissionid ...

How to execute SQL queries from text files

sql,database,sybase,aquafold

I'm also not sure of how to do this in Aqua, but it's very simple to create a batch/powershell script to execute .sql files You can use the SAP/Sybase isql utility to execute files, and just create a loop to cover all the files you wish to execute. Check my...

Master Device Is Lost and Valid Dump Does Not Exist (SYBASE ASE 15.0)

database,tsql,sybase,sybase-ase

If the devices of the other databases are not damaged and you have the device creation scripts you can restore the devices with disk reinit and disk refit , after that just query the users from each database from sysusers , get the name , the creation id and add...