texts: Defining the structure of data that is composed of several values

texts:
By this question I mean something like this:

For example a student has a student ID of 31080014. This student ID is a combination of information with the breakdown as follow

3 -> Information Technology Department
1 -> Software Engineering Study Program
08 -> Year entering, 08 means 2008
0014 -> 14th student when the name is ordered from A to Z. Ordering is done after the final admission wave has been completed. Additional student, like if a student transfers in the middle of the semester will be given the next available number. So for example if there are 200 students, the 201th student (the transfer student) will be given the Student ID of 31080201 regardless of his name.

What is the best practice to structure this? Should I just make one column called StudentID with type of varchar? Which means that if I have a program that filters Student by their Department, then my query would have to use LIKE 3% and be indexed.

Or should I arrange them into separate columns? Please also explain why one is the better practice than the other.


texts:
I am trying to use psql from a Windows command prompt (cmd.exe), but can't access any of the previous commands with the up/down cursors. I could also really do with finding out where the PostgreSQL equivalent to .bash_history is (if there is one), as I could read the command history from there.

\s produces the following error:

  history is not supported by this installation

Is there a way to get the psql command history on Windows?


texts: SELECT COUNT(*) on a view is orders of magnitude slower than SELECT * on the same view

texts:
The View
CREATE VIEW [dbo].[vProductList]
WITH SCHEMABINDING
AS

SELECT
     p.[Id]
    ,p.[Name]
    ,price.[Value] as CalculatedPrice
    ,orders.[Value] as OrdersWithThisProduct
FROM
    products as p
    INNER JOIN productMetadata as price ON p.Id = price.ProductId AND price.MetaId = 1
    INNER JOIN productMetadata as orders ON p.Id = orders.ProductId AND orders.MetaId = 2

For the sake of simplicity, assume that productMetadata has columns ProductId, MetaId, Value has ~87m rows, and there are about 400k rows in the products table.
General queries against this view work perfectly:
SELECT * FROM vProductList WHERE CalculatedPrice > 500

Query results in 2-4 seconds (over a vpn and remote, so I'm good with that).
Changing the above to a count is equally fast:
SELECT COUNT(*) from vProductList WHERE CalculatedPrice > 500

runs in about the same time as the raw select, which again I'm OK with. There are roughly 10k products meeting this criteria.
I've run into two separate instances where things get really odd and take FOREVER.
First
Doing a query against one of the columns from the base table in the view:
SELECT * FROM vProductList WHERE Name = 'Hammer'

This query takes a little while to run (20-30 seconds) and returns ~30k results; however, a slight change to said query:
SELECT COUNT(*) FROM vProductList WHERE Name = 'Hammer'

takes thirteen MINUTES to return a count stating ~30k .
Second
Doing a WHERE IN subquery
SELECT * FROM vProductList WHERE Id IN (SELECT ProductId FROM TableThatHasFKToProductId and ColumnInTable = 'Yes')

This query returns ~300k rows and takes two minutes to return (much of that time is simply spent downloading the data into SSMS I believe); however, changing that to a SELECT COUNT(*) results in a query that takes twenty minutes.
SELECT COUNT(*) FROM vProductList WHERE Id IN (SELECT ProductId FROM TableThatHasFKToProductId and ColumnInTable = 'Yes')

Why is it that SELECT * is faster than SELECT COUNT?
I am using the Total Execution Time provided by SSMS for all timings listed here.
Execution plans

Plan for SELECT 1 FROM v WHERE IN (...)
Plan for SELECT COUNT(0) FROM v WHERE IN (...)

Note: I tried using PasteThePlan but it kept telling my the plan was invalid xml.


texts: I have a big confusion between Always On FCI/AG. I know that for FCI you need to have shared disks and in-case of Hardware failure or OS failure or System reboot the instance with db, logins etc will fail-over to another node. Plus instance is constant through the setup. For AG i am not clear when db fail-over occur ? what if hardware fails or OS fails or System reboots ? What instance names i must configure for all nodes ? What about logins we need to take care about ?


texts: Handling variable fields in a CSV file with JSON?

texts:
Im trying to figure out the engineering to get a CSV file into PostgreSQL.
Requirements:

The CSV has X number of predetermined required fields
The CSV file has Y number of dynamic fields

Example:

TenantCode,DomainCode,PersonCode,ExtraField1,ExtraField2,ExtraFieldN
"CUST1","STUD","C0001","Donald","Duck","M"
"CUST1","STUD","C0002","Diana","Duck","F"

into this format (in PostgreSQL 10)

TenantCode---DomainCode---PersonCode---ExtendedFields(JSONB)

CUST1---STUD---C0001---  {"ExtraField1":"Donald","ExtraField2":"Duck","ExtraFieldN":"M"}
CUST1---STUD---C0002---   {"ExtraField1":"Diana","ExtraField2":"Duck","ExtraFieldN":"F"}

My original thought would be to (in Python) use Pandas to convert the entire file to json, then use PostgreSQL to COPY into a staging table, then use PostgreSQL SQL to parse out the required fields and insert into the destination table.


texts: Delete millions of rows from a SQL table

texts:
I have to delete 16+ millions records from a 221+ million row table and it is going extremely slowly.

I appreciate if you share suggestions to make code below faster:

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

DECLARE @BATCHSIZE INT,
        @ITERATION INT,
        @TOTALROWS INT,
        @MSG VARCHAR(500);
SET DEADLOCK_PRIORITY LOW;
SET @BATCHSIZE = 4500;
SET @ITERATION = 0;
SET @TOTALROWS = 0;

BEGIN TRY
    BEGIN TRANSACTION;

    WHILE @BATCHSIZE > 0
        BEGIN
            DELETE TOP (@BATCHSIZE) FROM MySourceTable
            OUTPUT DELETED.*
            INTO MyBackupTable
            WHERE NOT EXISTS (
                                 SELECT NULL AS Empty
                                 FROM   dbo.vendor AS v
                                 WHERE  VendorId = v.Id
                             );

            SET @BATCHSIZE = @@ROWCOUNT;
            SET @ITERATION = @ITERATION + 1;
            SET @TOTALROWS = @TOTALROWS + @BATCHSIZE;
            SET @MSG = CAST(GETDATE() AS VARCHAR) + ' Iteration: ' + CAST(@ITERATION AS VARCHAR) + ' Total deletes:' + CAST(@TOTALROWS AS VARCHAR) + ' Next Batch size:' + CAST(@BATCHSIZE AS VARCHAR);
            PRINT @MSG;
            COMMIT TRANSACTION;
            CHECKPOINT;
        END;
END TRY
BEGIN CATCH
    IF @@ERROR <> 0
       AND @@TRANCOUNT > 0
        BEGIN
            PRINT 'There is an error occured.  The database update failed.';
            ROLLBACK TRANSACTION;
        END;
END CATCH;
GO

Execution Plan (limited for 2 iterations)

VendorId is PK and non-clustered, where clustered index is not in use by this script. There are 5 other non-unique, non-clustered indexes.

Task is "removing vendors which do not exist in another table" and back them up into another table.  I have 3 tables, vendors, SpecialVendors, SpecialVendorBackups.  Trying to remove SpecialVendors which do not exist in Vendors table, and to have a backup of deleted records in case what I'm doing is wrong and I have to put them back in a week or two.


texts: Create a mysql replication from multiple master and single slave server?

texts:
How to create mysql replication with multiple master server and with single slave server? how to edit the "CHANGE MASTER TO MASTER_HOST='12.34.56.789',MASTER_USER='slave_user', MASTER_PASSWORD='password', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=  107;
" command to channelise different master db at the same time.


texts: Auto generated statistics by query optimiser

texts:
Can we consider removing the auto generated statistics if the update statistic within a given database is taking too long? We do a weekly maintenance using UPDATE STATISTICS WITH RESAMPLE but often have to stop the script as it overruns into business hours.

When is it most suitable to use RESAMPLE and FULLSCAN?


texts:
I'm using oracle database

I need to display customer id and customer name of those customers who have not purchased at all from any retail outlet using not exists

My query:

select customerid,customername
from customer c
where  not exists (
select p.customerid
from purchasebill p,retailoutlet r,customer c
where p.customerid=c.customerid and p.retailoutletid=r.retailoutletid group by p.customerid);

I supposed to get 3 records but i'm getting output as no rows selected, instead of not exists if i use not in, the output is correct.What's wrong in the above code please let me know.


texts: How to find the current value for 'log_history' of PURGE_LOG job in Oracle

texts:
I was trying to find what is the value of 'log_history' of PURGE_LOG job in Oracle.
I found that you can set the value using following command.

DBMS_SCHEDULER.SET_SCHEDULER_ATTRIBUTE
('log_history',<NUMBER_OF_DAYS>);

But I was not able to find any way to get what is the current value of this property.
Could you please let me know if there is a way to do this?

Thanks in Advanced.


texts: mariadb won't start (InnoDB error: space header page consists of zero bytes in data file ./ibdata1)

texts:
The following messages are in /var/log/mariadb/mariadb.log when I try to start mariadb:

151103 13:42:01 [Note] InnoDB: Initializing buffer pool, size = 128.0M
151103 13:42:01 [Note] InnoDB: Completed initialization of buffer pool
151103 13:42:01 [Note] InnoDB: Restoring page 0 of tablespace 0
151103 13:42:01 [Warning] InnoDB: Doublewrite does not have page_no=0 of space: 0
151103 13:42:01 [ERROR] InnoDB: space header page consists of zero bytes in data file ./ibdata1
151103 13:42:01 [ERROR] InnoDB: Could not open or create the system tablespace. If you tried to add new data files to the system tablespace, and it failed here, you should now edit innodb_data_file_path in my.cnf back to what it was, and remove the new ibdata files InnoDB created in this failed attempt. InnoDB only wrote those files full of zeros, but did not yet use them in any way. But be careful: do not remove old data files which contain your precious data!
151103 13:42:01 [ERROR] Plugin 'InnoDB' init function returned error.

This error seems similar to this one (https://stackoverflow.com/questions/26576745/innodb-error-space-header-page-consists-of-zero-bytes-in-data-file-ibdata1
), but this is a brand new Fedora 22 system and the database has not been used, yet.


texts: Inserting into multiple tables simultanteously (HEIDISQL)

texts:
I have three tables.

flatpack_apn

components_apn

flatpackcomponents_apn

CREATE TABLE `component_apn` (
`componentno` INT(11) NOT NULL AUTO_INCREMENT,
`description` VARCHAR(30) NOT NULL,
PRIMARY KEY (`componentno`)
)
COLLATE='hp8_english_ci'
ENGINE=InnoDB
;

CREATE TABLE `flatpackcomponents_apn` (
`flatpackid` INT(11) NOT NULL,
`componentno` INT(11) NOT NULL,
`quantity` INT(10) NOT NULL,
PRIMARY KEY (`componentno`, `flatpackid`),
INDEX `flatpackid` (`flatpackid`),
INDEX `componentno` (`componentno`),
CONSTRAINT `flatpackcomponents_apn_ibfk_1` FOREIGN KEY (`flatpackid`) REFERENCES `flatpack_apn` (`flatpackid`),
CONSTRAINT `flatpackcomponents_apn_ibfk_2` FOREIGN KEY (`componentno`) REFERENCES `component_apn` (`componentno`)
)
COLLATE='hp8_english_ci'
ENGINE=InnoDB
;

CREATE TABLE `flatpack_apn` (
    `flatpackid` INT(11) NOT NULL AUTO_INCREMENT,
    `name` TINYTEXT NOT NULL,
    `colour` TEXT NULL DEFAULT NULL,
    `flatpacktype` ENUM('Office','Kitchen','Bedroom','General') NOT NULL,
    `unitprice` DECIMAL(5,2) NULL DEFAULT NULL,
    PRIMARY KEY (`flatpackid`)
)
COLLATE='hp8_english_ci'
ENGINE=InnoDB
AUTO_INCREMENT=2123
;

I am asked to:

Populate the tables with sample data using suitable data values and choosing values which are not offensive.
Flatpack: at least 5 flatpacks with at least 2 having more than 3 components
Component: at least 10 components

I am a little uncertain as to how to this.

I thought that:

INSERT INTO flatpack_apn
SELECT *FROM components_apn

INSERT INTO flatpack_apn

SELECT quantity FROM flatpackcomponents_apn;

Is it possible to run them simultanteously, or would I need to execute them as two seperate events? Is there any downside to simply running them as two seperate insert events? In truth, I am not entirely sure I am interpreting the question above correctly.

As a sidebar; and apologies if I am asking too much in one question, but, I figured that I would be required to create/insert new columns within the table before adding the rows/records. Would this be a correct assumption?


texts: Are "create user" and "grant connect" functionally equivalent?

texts: I don't have a problem per se, but I've noticed that if I grant a database permission to a principal that doesn't exist in the database, the permission shows up in sys.database_permissions but that the user cannot connect to the database (as expected). If I then grant connect to the user, everything is fine. Which made me wonder if create user and grant connect were functionally equivalent. That is, is there anything additional that create user does that grant connect doesn't?


texts: Oracle shared memory exception ORA-04031

texts:
I'm trying to establish an Oracle database connection. It throws the following exception:

ORA-04031: unable to allocate 4048 bytes of shared memory
  ("shared pool","unknown object","sga heap(1,0)","kglsim heap")

I have tried connecting the database through JDBC as well as  SQL Developer, however it throws the exception in both case.

How can I fix this?


texts: How to Locate Resource System Database in MS SQL Server

texts:
There is a database called Resource database, which I know that is read only and contains information about system objects and does not save any user related data or metadata. I am using SSMS to manage my database instance and I can only see four system databases - master, model, msdb, tempdb. I'd like to know where can I find Resource database using SSMS? Or is Resource database intentionally invisible for users?

Thanks for your time.


texts: Collection of different types of data in a relational database

texts:
Let's say I have a table Cars (with columns such as model, make, year, etc.), a table Books (with columns such as name, author, ISBN, etc.) and a table Wines (with columns such as name, appellation, region, vintage, etc.)

Now, even if these tables don't seem related at all, I want to make a collection of all these things called "My favorites things in the world". In this list, I will find cars, books and wines.

If I only wanted to have lists of cars, I would create a table called Car_Lists with id and name + a table called Car_List_Items with id, car_list_id (foreign key to the id in the table Car_Lists) and car_id (foreign key to the id in the table Cars).

Here, as I want to have a list of different types of things, I don't really see how this could be done.

So my question: How would I represent that with a relational database?


texts: How to access different MySQL databases in a same instance in Amazon RDS?

texts:
In my Amazon RDS Dashboard, the configuration details of my DB instance (running MySQL) mentions that my DB name is ebdb, which is the default one, I haven't changed it. It's the same screen as here, instead that I don't want to change the instance name.

I've managed to connect to this DB using MySQL Workbench and I've imported a schema carpool which now lies alongside the existing ebdb one:

In the config of my app I've specified this DB url: jdbc:mysql://${DB_HOST}:3306/carpool, DB_HOST being the endpoint specified in the RDS Dashboard.

If my app tries to access the DB it throws an error because it can't find the table. I assume that's because it looks in the wrong database.

In the RDS Dashboard, placing the cursor on the label DB Name gives some info, including An Amazon DB instance can host multiple databases. Then why is the name of the database hard-coded ebdb ?

How can I access another database in my RDS instance ?


texts: How can I mount vss snapshots and why we have to do that to take backup in window server backup?

texts:
"Windows Server Backup cannot guarantee Volume Shadow Copy (VSS) snapshots are retained. In certain situations, after restoring to a new volume, the VSS snapshots may no longer be recognized and appear inaccessible. To guarantee protection of a VSS snapshot, mount the VSS snapshot, and run a backup job on that snapshot." It's written in the doc " https://docs.microsoft.com/en-us/previous-versions/windows/it-pro/windows-server-2012-R2-and-2012/jj614621(v=ws.11) what's this pharagraph suggesting can someone please explain it to me? I tried to find online but found very little information.

My questions :

1) why we have to protect vss snapshot, does not window server backup feature takes care of it ? If no then how can I do it?


texts: Scenario to automate the promotion of a read replica when the master db is not responding

texts:
I would like to set a scenario like the following:

Set an RDS event subscription [that the MySQL is unresponsive]
that will publish to an SNS topic
that will trigger a Lambda function
which will run a script that will promote a read replica [in another region] to master

Is such a case feasible?

Which DB metric should I monitor to confirm that the DB is not responding and that there is no false alarm?

Would it be dangerous to automate the promotion of the read replica? Should such actions remain manual and just add alerts?


texts: Protocol used by Oracle RMAN to communicate with auxiliary

texts:
What protocol is used by RMAN to communicate with the server used for backing up/restoring data?

I searched everywhere but couldn't find any docs with this information.

Background: I am developing a proxy that backs up an oracle database to a different store


texts: How can I find and clean up unused database files?

texts: How can I find and clean up unused (i.e. not used by any attached database) SQL Server database files (.mdf, .ldf, .ndf) that take up extra space on my server?


texts: Oracle: How do I query a Hierarchical table?

texts:
Background

This is for the construction of some views we'll be using for reporting.

I have an table of locations, the key fields being "location" and "parent".

The structure that these two fields create, level-wise, are along the lines of Company Name --> Campus Name --> Building Name --> Floor Name --> Room Name. Company name remains the same and Campus name remains the same in this case.

The structure of locations generally looks like this:

                                 +-----------+
                                 | Org. Name |
                                 +-----+-----+
                                       |
                                 +-----v-----+
           +--------------------+|Campus Name|+---+--+-------------+
           |                     +--+--------+    |                |
           |                        |             |                |
           |                        |             |                |
        +--+-----+           +------+-+        +--+----+       +---+---+
    +--+| BLDG-01|+--+       | BLDG-02|        |BLDG-03|       |Grounds|
    |   +--------+   |       +--------+        +-------+       +-------+
  +-+------+   +-----+--+
  |Floor-01|   |Basement+-------+
  +-+------+   +--------+       |
    |                           |
    |                           |
    | +----------+      +-------+--+
    +-+Room 1-001|      |Room B-002|
      +----------+      +----------+

Every location links back to its parent location, which is ultimately the organization name. Currently, there is only one organization and one campus.

Goals

I would like to be able to query all locations beneath any given location at the "Building" level. This is so I can return things like how many workorders have been performed for any location within a given building.
I would like to be able to determine which sub-location belongs to which building. Essentially the reverse; I would like to go from any level beneath the building level and trace back up to what the building is.
I would like this to be in a view. That means, I would like to have a table that for every item at the "building" level, lists the building in the left-hand column and all possible locations UNDER that building in the right-hand column. This way I'd have a list that I could query at any time to find which locations are part of which building.

Attempts and Doing it Right

I've attempted to do this through horribly constructed views, UNION queries, etc. -- which all have seemed like a bad idea. I know Oracle possesses a mechanism for this through "CONNECT BY"; I'm just not sure how to make use of it.


texts: Performance of inner join query for certain condition

texts:
I have one question about inner join query in SQL Server 2008. Is there any situation where NOT to use inner joins.
I have one scenario please tell me that here inner join is fruitful or not.

I have 2 tables

Table1 Columns: T1Id, Code and Name1, (Here Code is unique column)
Table2 Columns: T2Id, T1id(reference from Table 1), Name2, Type

Now there can be millions of records in both table. Now user pass the Code and I have to search the Name2 3 times in same procedure for diff condition.
I can do it with 2 scenario.

Scenario 1

I can store T1Id in temp variable

Declare @Id Int
Select @Id=Top 1 T1Id from Table1 Where Code='ABC'

Step 2: I will write all my queries

Select Name2 From Table2 Where T1id=@Id and Type='A'
Select Name2 From Table2 Where T1id=@Id and Type='B'
Select Name2 From Table2 Where T1id=@Id and Type='C'

Scenario 2:

Select Name2
from Table2 Inner join Table1 on Table1.T1Id = Table2.T1Id
Where Table2.Type='A' and Table1.Code='ABC'

Select Name2
From Table2 Inner join Table1 on Table1.T1Id = Table2.T1Id
Where Table2.Type='B' and Table1.Code='ABC'

Select Name2
From Table2 Inner join Table1 on Table1.T1Id = Table2.T1Id
Where Table2.Type='C' and Table1.Code='ABC'

Please tell me which one is good to use. Both tables can have more than millions records in future.


texts: Should roles without login have the connect permission on a database?

texts:
I want to create a read-only role in PostgreSQL and use it to assign it to multiple new users, who should have read-only access. It makes sense to me to also grant the read-only permissions to the role on the target DB. My concern is, whether the role, created with 'NOLOGIN' should have the 'CONNECT ON DATABASE' permission. Or is it better for some reason to grant the connect permission only to individual users with logins?
The role:
CREATE ROLE role_readonly WITH
    NOLOGIN NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION VALID UNTIL 'infinity';

Permissions:
# should this be here?
GRANT CONNECT ON DATABASE db_name TO role_readonly;
#
GRANT USAGE ON SCHEMA public TO role_readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO role_readonly;
GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO role_readonly;


texts: SQL Continuous Table Updating: Best Practices?

texts:
I have a highly "vertical" database, and I've written a dynamic pivot script to build a new time-indexed table that I can actually query on human time scales.  Pivoting the table takes a few minutes per hour of data.

I would like to keep the new table up-to-date in an efficient way, and I was thinking of writing a script that executes every minute to pivot data, append to the new table, and delete the original entries.  I want to keep the new data forever.

Are there any SQL server 2014 features/pitfalls I should be aware of, and is there a better path than the one I'm about to head down?  I've only been scripting in SQL for about 48 hours now...

Thanks everyone.

Original Data:
10:30:00 A 1.0
10:30:01 A 1.1
10:30:02 A 1.2
10:30:00 B 2.1
10:30:01 B 2.3
10:30:02 B 2.5
10:30:00 C 3.4
10:30:01 C 3.3
10:30:02 C 3.2

New table:
           A    B    C
10:30:00  1.0  2.1  3.4
10:30:01  1.1  2.3  3.3
10:30:02  1.2  2.5  3.2

In this example, my question is how to deal with 10:30:03 data efficiently when it arrives.


texts: Comparing explain plains and execution time

texts:
Please consider the following 2 queries (which yield the same result), and their corresponding explain plan output:

Query A:

select max(registro)
from telemetria_data.historico_353871042915279
where clase=40
  and registro > now()- interval '5 days'

Explain Analyze verbose A:

Aggregate  (cost=27133.10..27133.11 rows=1 width=8) (actual time=570.304..570.304 rows=1 loops=1)
  Output: max(registro)
  ->  Seq Scan on telemetria_data.historico_353871042915279  (cost=0.00..27132.90 rows=81 width=8) (actual time=561.219..568.737 rows=6537 loops=1)
        Output: imei, utc, clase, valor, registro, power, voltaje
        Filter: ((historico_353871042915279.clase = 40) AND (historico_353871042915279.registro > (now() - '5 days'::interval)))
        Rows Removed by Filter: 807478
Planning time: 0.191 ms
Execution time: 570.336 ms

Query B:

select registro
from telemetria_data.historico_353871042915279
where clase=40
  and registro > now()- interval '5 days'
order by registro desc limit 1

Explain Analyze verbose B:

Limit  (cost=27133.31..27133.31 rows=1 width=8) (actual time=570.926..570.926 rows=1 loops=1)
  Output: registro
  ->  Sort  (cost=27133.31..27133.51 rows=81 width=8) (actual time=570.925..570.925 rows=1 loops=1)
        Output: registro
        Sort Key: historico_353871042915279.registro
        Sort Method: top-N heapsort  Memory: 25kB
        ->  Seq Scan on telemetria_data.historico_353871042915279  (cost=0.00..27132.90 rows=81 width=8) (actual time=561.216..568.773 rows=6543 loops=1)
              Output: registro
              Filter: ((historico_353871042915279.clase = 40) AND (historico_353871042915279.registro > (now() - '5 days'::interval)))
              Rows Removed by Filter: 807479
Planning time: 0.103 ms
Execution time: 570.949 ms

How can I actually know which query is faster? Or I'll just have to run a load test for the two of them and then compare results? Or perhaps any ideas/comments on making them faster?

PS: Please consider there is a minimal (1) row count different in the two explain plans as the result is taken from a live production database where records are being inserted.

Database:

select version()
PostgreSQL 9.4.9 on x86_64-unknown-linux-gnu, compiled by gcc (Debian 4.9.2-10) 4.9.2, 64-bit

OS:

lsb_release -a
No LSB modules are available.
Distributor ID: Debian
Description:    Debian GNU/Linux 8.6 (jessie)
Release:    8.6
Codename:   jessie

uname -a
Linux ip-10-1-1-65 3.16.0-4-amd64 #1 SMP Debian 3.16.36-1+deb8u2 (2016-10-19) x86_64 GNU/Linux


texts: Manually set log file size after shrink SQL Server 2008 R2

texts:
Am becoming a somewhat involuntary DBA at work at themoment and really need some help on something.

We have a 40GB database in Full Recovery Mode, no log backup configured and a huge log file of 84GB.  My plan thus far to salvage this situation is to run a full log backup on the database, shrink the log file and instigate a maintenance plan to run a log backup every night with the database backup to help keep it under control.

My problem is I do not want the log file to shrink down to nothing and spend the first morning on Monday constantly growing.  I have a rough estimate as to what the file should be (about 20% of the database) and would like to set this from the get-go to ensure as much contiguous space as possible.  Is this just a case of changing "Initial Size" under database Properties -> Files?  I would guess as well that the database would need to be offline for this to occur?

Thanks in advance


texts: Multiple system.replset collections in local

texts:
I have a replicaset that's showing multiple system.replset collection in the local db.

replset1:PRIMARY> show collections;
me
oplog.rs
replset.minvalid
slaves
system.indexes
system.profile
system.replset
system.replset

Running db.system.replset.find()  returns the documents from both with out error.

They are different versions, one showing the current membership, another an older setup.  I haven't seen this before.  Is this expected?

I'm not really seeing any problems from the mongo or application side, this just seems strange.  The duplicates are present on the primary and secondaries.

Should .remove({"version":}) be safe?


texts: PostgreSQL, could not connect to remote server via port 5432

texts:
I know other people have posted similar questions but none of the solutions applied here. I have installed Postgresql-9.6 under CentOS 7 on my virtual private server, and I am trying to connect to the server locally by doing:

psql -U postgres -h [ip_address]

However, I got message like:

could not connect to server: Connection refused Is the server running on
[ip_address] and accepting TCP/IP connections on port 5432?.

As suggested by previous posts, I went to postgresql.conf and set:

listen_addresses = '*'
port = 5432

also, I edited pg_hba.conf as follows:

# "local" is for Unix domain socket connections only
local   all             all                                     md5
# IPv4 local connections:
host    all             all             0.0.0.0/0               md5
# IPv6 local connections:
host    all             all             ::/0                    md5

By restart the postgresql server, and doing:

netstat -nlt

I got:

Proto Recv-Q Send-Q Local Address           Foreign Address         State
tcp        0      0 0.0.0.0:3306            0.0.0.0:*               LISTEN
tcp        0      0 0.0.0.0:22              0.0.0.0:*               LISTEN
tcp        0      0 0.0.0.0:5432            0.0.0.0:*               LISTEN
tcp        0      0 127.0.0.1:25            0.0.0.0:*               LISTEN
tcp        0      0 0.0.0.0:6969            0.0.0.0:*               LISTEN
tcp6       0      0 :::80                   :::*                    LISTEN
tcp6       0      0 :::22                   :::*                    LISTEN
tcp6       0      0 :::5432                 :::*                    LISTEN
tcp6       0      0 ::1:25                  :::*                    LISTEN

It shows port 5432 is listening. However, I still got same error message when trying to connect to the database locally.


texts:
I created a simple index associated with a timestamp column

CREATE TABLE `data` (
    `id` INT(11) UNSIGNED NOT NULL,
    `ticker` VARCHAR(16) NOT NULL COLLATE 'utf8_unicode_ci',
    `comment` TEXT NOT NULL COLLATE 'utf8_unicode_ci',
    `link` VARCHAR(256) NULL DEFAULT NULL COLLATE 'utf8_unicode_ci',
    `comment_hash` CHAR(32) NOT NULL COLLATE 'utf8_unicode_ci',
    `time` DATETIME NOT NULL,
    `feed` VARCHAR(16) NOT NULL COLLATE 'utf8_unicode_ci',
    `source` INT(11) NOT NULL,
    `active` TINYINT(1) NOT NULL DEFAULT '1',
    `score` VARCHAR(20) NULL DEFAULT NULL COLLATE 'utf8_unicode_ci',
    `dttm` DATETIME NULL DEFAULT NULL,
    PRIMARY KEY (`ticker`, `comment_hash`),
    INDEX `time_index1` (`time`)
)
COLLATE='utf8_unicode_ci'
ENGINE=InnoDB;

However when I try to do anything with the index, it gives me SQL Error (1054): Unknown column 'time_index1' in 'where clause'

Sample queries:

select *
from data
where time_index1 = '2013-10-01 17:18:06'

or

select *
from data
order by time_index1


texts: Always On Availability Groups on Windows 2012 Standard

texts:
I am trying to enable the Always On Availability Groups feature of SQL Server 2012 Enterprise Edition on Windows Server Standard Edition 2012, but it says the feature requires X64 Enterprise Edition of SQL Server 2012 or Windows 2008 Enterprise edition or above.

I already have Windows Standard 2012 and SQL Server 2012 Enterprise Edition, why can't I enable this feature?


texts: How to connect to oracle 12c from sqlplus without password in command line

texts:
This may seem as weird question:

If i have user called demo with password Pass1234

When connecting to oracle 11g I can run sqlplus like this with password in command line:
on DB host:

C:\> sqlplus demo/Pass1234

Or remote machine

C:\> sqlplus demo/Pass1234@<ip>:1521/orcl

I can do so without give the password in command line and expose it like this:

C:\> sqlplus demo

Then I get:

SQL*Plus: Release 11.1.0.7.0 bla bla bla
Copyright (c) bla bla bla
Enter password:

Then I can enter the password manually.

When connecting to Oracle 12c I can connect like this from any machine (with password):

C:\> sqlplus demo/Pass1234@<ip>:1521/pdborcl

My question is: How to connect to Oracle 12c without tnsnames.ora edit and without write the password in the command line?

I also havn't figured out yet if it is possible to connect to Oracle 12c from sqlplus on the DB host machine without give the IP or localhost, and without editing the tnsnames.ora, like I can do in Oracle 11g?

And more thing, can I do so from a remote machine (even to Oracle 11g) without using a full connect?

(means to give user and db connection in command line and password manually, not like use /NOLOG in command and then: SQL> connect demo/Pass1234@<ip>:1521/pdborcl)

Thanks!


texts: how to set/change the font style in database?

texts:
This question may be duplicate with another but my question is this.

I want to set or change the font style in header of table (like hindi font or any other) of database. I am using sql server 2008 on my system. I have no idea to changed the font (like kruti dev 010 or dev lys 010 or etc). I want some headers of the table in english and some other in hindi language.

I hope this information will be complete to understand.

please reply if any one have some ideas about such kind of things.

thanks


texts: How does MOVE COMPRESS affect future data?

texts:
I'm working with Oracle 11g and a requirement came down the pipeline to "[C]ompress our data [sic]." And I'm wondering how compression will affect future data.

The actual PL/SQL isn't an issue (something along these lines:
ALTER TABLE SOME_TABLE MOVE COMPRESS;), but I'm told by the DBA that MOVE COMPRESS will only compress the existing data, and that any further transactions will require explicit compression. After running that line, looking at the table's storage properties, Table Compression is properly set and everything, but that leads me to believe that this will go on in the background.

I guess my question is this: Do I need to run MOVE COMPRESS (or something similar) after new transactions, or will Oracle compress the data going forward? If not, is there something I can amend to my ALTER TABLE statements that will enforce this?


texts: Will SSRS User rights be over-ridden by Windows account?

texts: If a Reporting Services user group has all rights on SRS Report Server folder, and one of the group member's Windows Account has been added to the folder with Browse only, will SQL server cause that member's rights to be over-ridden by the Window's account Browse rights?


texts:
The following case expression results in a,

select case 0 when true  then 'c' when 'true1222' then 'a' else 'b' end;

Anyone knows why? How could 0 be equal to true1222?


texts: Database subsetting a viable method of slicing a database?

texts:
Interested in getting a smaller copy of a database for testing purposes as restoring the full database takes hours, that would include all rows from tables referenced. Trying to do this manually would seem impossible.

I've seen a few tools out there that do this, but not sure which one would work with SQL Server? And perhaps I'm going about this all wrong and should be trying to do something different.

Any experience using data subsetting or anything else I should be doing instead?

Thanks for your help in advance.


texts:
We have query that worked well in MyISAM. When we switched to InnoDB, the writes I/O raised up to 50 times.

The query is type of INSERT ... ON DUPLICATE KEY UPDATE

I don't know if the problem is specific to ON DUPLICATE. Maybe we should try to update and if no rows were changed - do an insert. I guess MyISAM is doing exactly the same thing.

We tried to change various parameters for InnoDB Storage Engine according to recommendations on this site, but nothing helped.

Here is our configuration:

Variable_name   Value
innodb_adaptive_flushing    ON
innodb_adaptive_hash_index  ON
innodb_additional_mem_pool_size  8388608
innodb_autoextend_increment 8
innodb_autoinc_lock_mode    1
innodb_buffer_pool_instances    1
innodb_buffer_pool_size 8589934592
innodb_change_buffering all
innodb_checksums    ON
innodb_commit_concurrency   0
innodb_concurrency_tickets  500
innodb_data_file_path   ibdata1:10M:autoextend
innodb_data_home_dir
innodb_doublewrite  ON
innodb_fast_shutdown     1
innodb_file_format  Antelope
innodb_file_format_check    ON
innodb_file_format_max  Antelope
innodb_file_per_table   ON
innodb_flush_log_at_trx_commit  0
innodb_flush_method
innodb_force_load_corrupted OFF
innodb_force_recovery   0
innodb_io_capacity  200
innodb_large_prefix OFF
innodb_lock_wait_timeout    50
innodb_locks_unsafe_for_binlog   OFF
innodb_log_buffer_size  8388608
innodb_log_file_size    187695104
innodb_log_files_in_group   2
innodb_log_group_home_dir   .\
innodb_max_dirty_pages_pct  75
innodb_max_purge_lag    0
innodb_mirrored_log_groups  1
innodb_old_blocks_pct   37
innodb_old_blocks_time  0
innodb_open_files   300
innodb_purge_batch_size 20
innodb_purge_threads     0
innodb_random_read_ahead    OFF
innodb_read_ahead_threshold 56
innodb_read_io_threads  64
innodb_replication_delay    0
innodb_rollback_on_timeout  OFF
innodb_rollback_segments    128
innodb_spin_wait_delay  6
innodb_stats_method nulls_equal
innodb_stats_on_metadata    OFF
innodb_stats_sample_pages   8
innodb_strict_mode  OFF
innodb_support_xa    ON
innodb_sync_spin_loops  30
innodb_table_locks  ON
innodb_thread_concurrency   8
innodb_thread_sleep_delay   10000
innodb_use_native_aio   ON
innodb_use_sys_malloc   ON
innodb_version  1.1.8
innodb_write_io_threads 64


texts:
I'm using a command:

$ exp userid=apex_public_user/apex_public_user OWNER=apex_public_user DIRECT=Y CONSISTENT=Y FILE=/home/oracle/apex_public_user.dmp

and getting following output:

Export: Release 10.2.0.1.0 - Production on Fr May 18 11:55:20 2012

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Connected to: Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production
Export done in AL32UTF8 character set and AL16UTF16 NCHAR character set
. exporting pre-schema procedural objects and actions
EXP-00008: ORACLE error 4063 encountered
ORA-04063: package body "SYS.DBMS_REPCAT_UTL" has errors
ORA-06508: PL/SQL: could not find program unit being called: "SYS.DBMS_REPCAT_UTL"
ORA-06512: at "SYS.DBMS_REPCAT_EXP", line 87
ORA-06512: at line 1
EXP-00083: The previous problem occurred when calling SYS.DBMS_REPCAT_EXP.schema_info_exp
. exporting foreign function library names for user APEX_PUBLIC_USER
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions for user APEX_PUBLIC_USER
About to export APEX_PUBLIC_USER's objects ...
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
. about to export APEX_PUBLIC_USER's tables via Direct Path ...
. exporting synonyms
. exporting views
. exporting stored procedures
. exporting operators
. exporting referential integrity constraints
. exporting triggers
. exporting indextypes
. exporting bitmap, functional and extensible indexes
. exporting posttables actions
. exporting materialized views
. exporting snapshot logs
. exporting job queues
. exporting refresh groups and children
. exporting dimensions
. exporting post-schema procedural objects and actions
EXP-00008: ORACLE error 4063 encountered
ORA-04063: package body "SYS.DBMS_REPCAT_UTL" has errors
ORA-06508: PL/SQL: could not find program unit being called: "SYS.DBMS_REPCAT_UTL"
ORA-06512: at "SYS.DBMS_REPCAT_EXP", line 87
ORA-06512: at line 1
EXP-00083: The previous problem occurred when calling SYS.DBMS_REPCAT_EXP.schema_info_exp
. exporting statistics
Export terminated successfully with warnings.

What can be wrong with the way how I export a schema?
The same error appears while exporting any other schemas.


texts: How to better manage hard coded cross db references in stored procs?

texts:
I wanted to make a copy of a db and give it a different name, and have our application connect to this new copy of the db. The only problem is that other databases our application references have stored procs with hard coded references to the original db name. So even though I can make the application connect to the copy, any other databases it connects to will still connect to the original.

I have to believe this is probably a common problem that's been solved, but I'm wondering whether the solution is something I can still do on top of the implementation we have or if it's something that needed to be designed differently to begin with... and if so what is the "proper" design to handle this issue?


texts:
I'm have an issue with circular reference in my DB design. As see the ER diagram below have 2 circular reference that I highlighted with Red and Orange arrow.

Anyone know how to resolve this issue?


texts: "ENUM" is not vallid at his position, expecting ')'

texts:
I'm keeping this error and I'm very new to mySQL so I don't know what is going on. The only way I found to remove this error  message is to put ')' before the ENUM and then the message change - "ENUM" is not valid at this position, expecting EOF, ';'.
USE labo3_partie2;
CREATE TABLE fournisseur(
        numero SMALLINT UNSIGNED PRIMARY KEY,
        nom VARCHAR(40) NOT NULL,
        prenom VARCHAR(40),
        chiffreAffaire DECIMAL(7,2) CHECK ((chiffreAffaire >= 20000) && (chiffreAffaire <= 1000000)),
        province CHAR(2) DEFAULT 'QC' ENUM('QC','ON','NB')
);


texts: errno: 150 "Foreign key constraint is incorrectly formed"

texts:
CREATE TABLE IF NOT EXISTS `Register_Per` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `Name` varchar(50) NOT NULL,
 `DOB` varchar(20) NOT NULL,
 `Gender` CHARACTER(8) NOT NULL,
 `ConNo` int(11) NOT NULL UNIQUE,
 `Email` varchar(50) NOT NULL,
    PRIMARY KEY (`id`)
 );

CREATE TABLE IF NOT EXISTS `login`(
`id` int(11) NOT NULL AUTO_INCREMENT,
`username` varchar(50) NOT NULL,
`password` varchar(20) NOT NULL,
    PRIMARY KEY (`username`),
FOREIGN KEY(`id`) REFERENCES `Register_Per`(`id`)
);

CREATE TABLE `images` (
 `id` int(11) NOT NULL AUTO_INCREMENT UNIQUE,
 `file_name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
 `uploaded_on` datetime NOT NULL,
 `submittedby` varchar(50),
 FOREIGN KEY(`submittedby`) REFERENCES `login`(`username`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

What to change in this..??


texts: Does TRUNCATE TABLE affects isolation level in Sql Server?

texts:
As I am working on the project which has SQL SERVER  as a database....I'm maintaining isolation level in my project where I enabled snapshot isolation level for my project....

As I have to make some dirty read, I am querying it with setting SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED..I've implemented it in stored proc...Here is short version of my stored proc

ALTER PROCEDURE [dbo].[proc_ReadDirtyData]
@id INT,
@mode VARCHAR(5)

AS
BEGIN
    --Read Dirty Data
    IF (@mode = 'RDD')
    BEGIN
            SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
            SELECT * FROM tbl_transaction
            WHERE id = @id;--@id foreign key in tbl_transaction.
                           --This read has been done during the transaction from C# Apps
    END
END

In my C# apps, following structure is maintained

snapshot transaction start

stage1 : tblmaster --generates id
stage2 :tblchild --inserts the data with above id as foreign key.After successfull inserting,
          --dirty read goes here applying where equal to above id for other operation

if(operationSuccessful)
{
     transactionCommit()
}
else
{
     transactionRollback();
}

snapshot transaction end

I am getting problem in stage2 after TRUNCATE operation on operated table...

I've been checking whether it makes correct dirty reads,so I used to clear the data of that specific table where dirty read has been occured...

But, the problem comes when I use TRUNCATE for clearing the table data....If I use TRUNCATE, I can't make dirty reads where as if I use DELETE, it perfectly works for dirty read....

Here, I want to make clear that I'm not making dirty read during TRUNCATE/DELETE operation...After TRUNCATE/DELETE operation only, I go for dirty read of fresh data......

Now, I'm curious that whether TRUNCATE affects internal isolation of that table or not....


texts: MariaDB Concurrent Connections with Update Statement

texts:
I have two high-performance servers (Xeon 2.1 Ghz, 64GB RAM, SSD 120GB) with private IPs (i.e 10.0.0*) and 100Mb/s bandwidth:

SERVER1: Nginx + PHP7.0-FPM (latest versions)
SERVER2: MariaDB (latest version stable)

The database has only 1 row and this is the table structure:

CREATE TABLE users (
       `id` int UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
       `added` int(11) DEFAULT 0,
       `email` VARCHAR(150) NOT NULL,
       `files` BIGINT DEFAULT 1,
       UNIQUE KEY `email` (`email`),
       INDEX email_index (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;

This is the PHP script that makes just one select and one update to the first (and only) item with ID 1:

$mysqli = new mysqli("p:10.0.XX.XX", "username", "password", "database");

if ($mysqli->connect_errno) {
    printf("Connect failed: %s\n", $mysqli->connect_error);
    exit();
}

echo "Connected to the database!"."\n";

$result = $mysqli->query("select files from users where id = 1");

if($result)
{
    $row = $result->fetch_array(MYSQLI_ASSOC);
}
else
{
    echo "
Failed to select user fields
";
    exit();
}

$mysqli->query("update users set files='".intval(intval($row['files'])+1)."' where id = 1");

$mysqli->close();

Here is the changes I made to the default MariaDB config file:

[mysqld]
skip-name-resolve
bind-address            = 10.0.XX.XX
max_connections         = 25000
max_allowed_packet      = 1G
innodb_buffer_pool_size = 55G

** Bin logs is disabled, flush trx is set to 0, general logging is disabled, only slow queries are logged **

** I am not interested in ACID or other redundancy options **

Everything works perfectly without the "update" query, and can handle up to 10K concurrent connections according to https://loader.io/ tests. The problem is when the PHP script has to do the update statement, looks like all "update" queries are queued in the mariaDB server and it takes a lot of time to finish, more than 30 minutes (I then rebooted the mariaDB server because didn't want to wait more). So my questions are:

1) Is there a way to make "update" statement to not be queued and to be processed directly?

2) How many concurrent connections can the 64GB RAM + SSD mariaDB server handle considering the "update" statement?


texts: "Access is denied" when connecting SSMS to Integration Services

texts:
I receive the following error when attempting to connect SSMS to Integration Services using a particular SQL Server cluster's network name:

  Connecting to Integration Services service on the computer 'FooDB' failed with the following error: "Access is denied."

  This error occurs when the computer has not been configured to allow remote connections through DCOM, or the user does have permission to access the SQL Server Integration Services service through DCOM.

This is a routine problem with a well-documented solution. For example, see the solutions here and here.

However, I've tried all the solutions I know of, and the problem remains.

In more detail, I've done the following:

Verified that the users connecting have the DCOM permissions listed in the articles linked to above on MsDtsServer100:

Launch and Activation Permissions: Allow Local Launch, allow Remote Launch, Local Activation, Remote Activation
Access Permissions: Allow Local Access, allow Remote Access
Configuration Permission: Allow Read

Confirmed with a packet sniffer that all traffic related to the connection is successfully coming through the firewall. The last packet shown before the TCP connection is torn down is a reply from the server containing the Windows status code for 'access denied' inside an MSRPC header.
Tested adding the users to the 'Distributed COM Users' group and/or the local administrators group, then restarting the servers. This allowed the users to connect to SSIS from SSMS using the local node names (FooDBN1, FooDBN2), but they still get an 'access denied' error when connecting to the the cluster network name (FooDB), which is what they're accustomed to using, and what works on our other clusters.

Also, I haven't found altering membership of these groups necessary on other clusters.

On the other clusters I've checked, I can connect SSMS to SSIS using the cluster name without any non-default configuration.

I realise this might be more appropriate for ServerFault and am OK with the question being migrated if needed, but it's also an SQL Server issue and I think users here might be more likely to have dealt with it before.

Platform details:

Windows Server 2008 R2 SP1
SQL Server 2008 R2 SP2
2-node active-passive cluster with a single SQL Server instance

Could anyone suggest what I should be looking at next here?

Update: this mysteriously just started working today, but only for members of the local administrators group. Nothing has changed as far as I can tell.


texts: Multiple SQL Server data files on same SAN disk

texts:
I'm currently in the process of creating a new database, and have previously only ever used a single data file and a single log file. I've done some research online regarding the benefits of multiple data files, but I've found mixed opinions regarding the benefits of multiple data files on a single disk. So my question is: if the only disk available to me is a SAN disk would I see any performance benefits (such as multithreading/parallel processing) from splitting the data into 4 separate files? Would I be better of with a single file?

Also would there be any performance benefit of separating the indexes (and possibly an archive table) into their own filegroups on separate files on the same disk? Would this only provide administrative benefits?

Thanks!


texts:
I have a table (created below) that contains 321 rows.

I expect the last query below to use the NonClustered Index and then a Key Lookup. However, instead it uses a Clustered Index scan. Only a single row is returned as expected.

Why does it do the scan rather than use the NonClustered Index? Is it because the table contains only 321 rows?

CREATE TABLE dbo.TestIndexSample
(
    Code char(4) NOT NULL,
    Name nvarchar(200) NOT NULL,
    ModifiedDate datetime NOT NULL CONSTRAINT [DF_TestIndexSample_ModifiedDate] DEFAULT GETDATE(),
    CONSTRAINT [PK_TestIndexSample_Code] PRIMARY KEY CLUSTERED(Code)
);
GO
CREATE NONCLUSTERED INDEX IX_TestIndexSample_Name
    ON dbo.TestIndexSample(Name);
GO

INSERT INTO dbo.TestIndexSample(Code, Name)
select CodeName, FullName
from dbo.SourceTest
GO

SELECT * FROM dbo.TestIndexSample

SELECT * FROM dbo.TestIndexSample where Code = 'X132EY'

SELECT * FROM dbo.TestIndexSample where Name = 'User A'


texts: how to select first 9 numbers from other table from database using sql query?

texts:
I want to fetch first 9 numbers from matching with other column please help me to find out thank you....?

SELECT MAINFILE.facno,GLMAST.fglhead
FROM MAINFILE
INNER JOIN GLMAST ON LEFT(MAINFILE.facno,9) like GLMAST.fglcode and GLMAST.fglhead='" & ComboBox2.Text & "' "

I am matching glcode='010000101' with facno='010000101000001' where i want to select first 9 numbers to match with glcode from facno...i hope my concept understood..


texts:
i got tables like this:

data_transaksi

tgl_trans

ket

sim_wajib

what i want to do is showing data from certain dates(tgl_trans) and ket values (values = SW,SS and penyesuian nilai tambah)
code that i used (for testing):
select tgl_trans, sim_wajib from data_transaksi
where ket = 'SW' or ket = 'SS' or ket = 'penyesuaian simpanan tambah'
and tgl_trans between '2021-01-01' and '2021-01-31'

expected output

tgl_trans
ket

2021-01-01
SW

2021-01-02
SP

---
--

2021-01-30
SW

2021-01-30
SP

but output is:

tgl_trans
ket

2021-01-01
SW

2021-01-01
SP

---
---

2021-02-01
SW

2021-02-01
SP

---
---

2021-03-01
SW

2021-03-01
SP

---
---

so its showing all the data where ket values = SW,SS and penyesuian nilai tambah
how to fix this?
any help will be appreciated!


texts: Shared connection pool in SQL Server and prevention of cross-database queries

texts:
Microsoft recommends that a shared connection pool should be used when running e.g. a SaaS software with one database per customer but with a shared codebase to prevent pool fragmentation:
https://msdn.microsoft.com/en-us/library/8xx3tyca%28v=vs.110%29.aspx

When taking out a connection from the pool the database to access should be first be changed by the command "USE Database_A".

However, as I see it, all the databases would then use the same login with let's say the db_datareader - role right.

Is there any easy way to prevent the user from making cross-database queries or directly querying other databases using a syntax like "SELECT * FROM Database_B.dbo.Person"?


texts:
I am working on a SQL Server on which I have three databases:

User: This is the one where real data resides
Test
Development: This is used for testing and developement.

From time to time I want to copy the content of the user database into the test and / or development database to make sure that the developement and test environments are as close as possible to the user environment.

I thought I could use the "Copy Database Wizard" for this, but it fails with an error

  "Object reference not set to an instance of an object

after I have chosen which hosts to copy from and to (localhost in both cases). I have not been able to find any solutions to this error.

Question

Does anybody have any ideas about what causes this and how to correct the problem (maybe I need to install something or do something with the setup of the server?)
What would be the selected way of doing the transfer if I cannot use the Copy Database Wizard? - This is to be done, I would guess, about once a month.

If it matters, I have full admin access on the database server but not on the underlying windows server.


texts:
I am trying to find and write a query for what database is affected by a SQL Server job.
I have checked the following:

Get a list of SQL Server Agent Jobs

and

SQL Server Agent Tables

and have been able to find the JobID, modified dates, etc, but can't find what database is being affected by the job. Is there anyway to find this?
I am trying to write a query that pulls info for SQL Server jobs, and this is the last part I need.

Here is the info I want to pull, and the query:

Here is my simple Query to find out SQL Server agent job info (I am sure this could be done better, so I am open to suggestions here as well):

It finds:

JobID
JobName
JobOwner
JobCreatedOn
JobLastModifiedOn
OriginatingServerName
IsEnabled
LastRunDateTime
LastRunStatus
LastRunDuraction (HH:MM:SS)
LastRunStatusMessage
DATABASE AFFECTED -- How to add this?

SELECT
    [sJOB].[job_id] AS [JobID]
    , [sJOB].[name] AS [JobName]
     , [sDBP].[name] AS [JobOwner]
     , [sJOB].[date_created] AS [JobCreatedOn]
    , [sJOB].[date_modified] AS [JobLastModifiedOn]
    , [sSVR].[name] AS [OriginatingServerName]
     , CASE [sJOB].[enabled]
        WHEN 1 THEN 'Yes'
        WHEN 0 THEN 'No'
        END AS [IsEnabled]
    , CASE
        WHEN [sJOBH].[run_date] IS NULL OR [sJOBH].[run_time] IS NULL THEN NULL
        ELSE CAST(
                CAST([sJOBH].[run_date] AS CHAR(8))
                + ' '
                + STUFF(
                    STUFF(RIGHT('000000' + CAST([sJOBH].[run_time] AS VARCHAR(6)),  6)
                        , 3, 0, ':')
                    , 6, 0, ':')
                AS DATETIME)
      END AS [LastRunDateTime]
    , CASE [sJOBH].[run_status]
        WHEN 0 THEN 'Failed'
        WHEN 1 THEN 'Succeeded'
        WHEN 2 THEN 'Retry'
        WHEN 3 THEN 'Canceled'
        WHEN 4 THEN 'Running' -- In Progress
      END AS [LastRunStatus]
    , STUFF(
            STUFF(RIGHT('000000' + CAST([sJOBH].[run_duration] AS VARCHAR(6)),  6)
                , 3, 0, ':')
            , 6, 0, ':')
        AS [LastRunDuration (HH:MM:SS)]
    , [sJOBH].[message] AS [LastRunStatusMessage]
FROM
     [msdb].[dbo].[sysjobs] AS [sJOB]
    LEFT JOIN [msdb].[sys].[servers] AS [sSVR]
        ON [sJOB].[originating_server_id] = [sSVR].[server_id]
    LEFT JOIN [msdb].[dbo].[syscategories] AS [sCAT]
        ON [sJOB].[category_id] = [sCAT].[category_id]
    LEFT JOIN [msdb].[dbo].[sysjobsteps] AS [sJSTP]
        ON [sJOB].[job_id] = [sJSTP].[job_id]
        AND [sJOB].[start_step_id] = [sJSTP].[step_id]
       LEFT JOIN master.sys.syslogins AS [sDBP]
          ON [sJOB].[owner_sid] = [sDBP].[sid]
    LEFT JOIN (
                SELECT
                    [job_id]
                    , MIN([next_run_date]) AS [NextRunDate]
                    , MIN([next_run_time]) AS [NextRunTime]
                FROM [msdb].[dbo].[sysjobschedules]
                GROUP BY [job_id]
            ) AS [sJOBSCH]
        ON [sJOB].[job_id] = [sJOBSCH].[job_id]
    LEFT JOIN (
                SELECT
                    [job_id]
                    , [run_date]
                    , [run_time]
                    , [run_status]
                    , [run_duration]
                    , [message]
                    , ROW_NUMBER() OVER (
                                            PARTITION BY [job_id]
                                            ORDER BY [run_date] DESC, [run_time] DESC
                      ) AS RowNumber
                FROM [msdb].[dbo].[sysjobhistory]
                WHERE [step_id] = 0
            ) AS [sJOBH]
        ON [sJOB].[job_id] = [sJOBH].[job_id]
        AND [sJOBH].[RowNumber] = 1
ORDER BY [JobName]


texts:
I tried to restore an unencrypted database to a different server and it failed with the error:

  "Cannot find certificate with thumbprint".

This suggests the database is encrypted but it is not.  There are other databases on this server that are encrypted, but not this one.

When I query the sys.dm_database_encryption_keys table the encryption_state for this database is 1 = Unencrypted.

I see other databases where the encryption_state value is NULL = Not Encrypted.

So I have 2 questions:

What the difference is between the values NULL and 1?
Why am I getting this error on an Unencrypted database?

Thank you for your time considering my question.


texts:
I'm trying to determine whether I can retrieve a property (attribute) value from an xml blob stored in a FILESTREAM-enabled column using Full-text search.  Here is a sample where message is a varbinary(max) field that stores xml messages:

SELECT message FROM AuditTable
WHERE CONTAINS ( PROPERTY ( message, 'AccountNo' ), '123456');

On the MSDN page Search Document Properties with Search Property Lists, the summary at the top of the page includes this statement: "On the other hand, the XML IFilter does not emit properties."

I'm confused because it appears that this should work for an XML document. I'm starting to prep an environment to test, but I wanted to ask first just in case someone already knew.


texts: How to search in blob binary wise, and how to export data binary wise

texts:
I've a table with a blob field for each row.
The blob contains binary data, which can be represented as bytes or text ( using mysql workbench - open value in viewer)

Does anybody know how can I search specific sequence of bytes in a given bytes chunk(blob field),
for example search for the sequence of bytes: 00 38 a7 in blob's binary representation
Is there any possibility to export all table values to csv with blob values in binary representation When I tried to export using mysql workbench I got for each blob ... as a value in csv.


texts: How to log logon logoff and user activity on mySQL server for audit?

texts:
My boss wants to have some kind of log on the server running mysql which would log user activity.

Kind of like:

User blah logged in
User blah ran some query
User blah logedd off
User asdf logged on
..and so on

I did some research and found out about triggers:
How to log successful login attempts to a database?

Also, is that information logged into the mysql general log?

More info:

mysql is installed on a server running Redhat
Installed mysql version is: 5.0.91

I'm not a DBA, so please go easy on me!
Thanx in advance!


texts: Why does adding a TOP 1 dramatically worsen performance?

texts:
I have a fairly simple query

SELECT TOP 1 dc.DOCUMENT_ID,
        dc.COPIES,
        dc.REQUESTOR,
        dc.D_ID,
        cj.FILE_NUMBER
FROM DOCUMENT_QUEUE dc
JOIN CORRESPONDENCE_JOURNAL cj
    ON dc.DOCUMENT_ID = cj.DOCUMENT_ID
WHERE dc.QUEUE_DATE <= GETDATE()
  AND dc.PRINT_LOCATION = 2
ORDER BY cj.FILE_NUMBER

That is giving me horrible performance (like never bothered to wait for it to finish).  The query plan looks like this:

However if I remove the TOP 1 I get a plan that looks like this and it runs in 1-2 seconds:

Correct PK & indexing below.

The fact that the TOP 1 changed the query plan doesn't surprise me, I'm just a bit surprised that it makes it so much worse.

Note: I've read the results from this post and understand the concept of a Row Goal etc.  What I'm curious about is how I can go about changing the query so that it uses the better plan.  Currently I'm dumping the data into a temp table then pulling the first row off of it.  I'm wondering if there is a better method.

Edit For people reading this after the fact here are a few extra pieces of information.

Document_Queue - PK/CI is D_ID and it has ~5k rows.
Correspondence_Journal - PK/CI is FILE_NUMBER, CORRESPONDENCE_ID and it has ~1.4 mil rows.

When I started there were no other indexes.  I ended up with one on Correspondence_Journal (Document_Id, File_Number)


texts: Does hard-coding schema names in procedure calls for Oracle jobs have any performance impact?

texts:
A database application I'm working with uses Oracle scheduled jobs to perform certain tasks. The DBA insists that hard-coding the schema name in the procedures that organise these jobs improves performance, but I can't see how it makes any difference. (I'm not even sure why it's necessary to have the schema name in there at all - the procedures run by the jobs are always in the same schema as the the job definer.)

Can there possibly be any truth to this? In general, does specifying the schema name make any difference when calling a procedure in the same schema as the connected user?


texts:
I have the following listener.ora file located in $ORACLE_HOME/network/admin/ :

TESTSID = (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SID_NAME = TESTSID)
    )
  )

When I run dbstart, I am able to connect to some of the instances, but not the TESTSID one specified. Also, there is no error when I change that listener.ora to be an invalid entry.

When I run lsnrctl status, the other instances are listed. After running lsnrctl stop and lsnrctl start I can no longer connect to any of them and they are missing when I check the status again. I believe that this means that the database instances are registering with the listener. However, when I run status both before and after restarting the listener, both state the following (which is the correct file):

Listener Parameter File  /u01/app/oracle/product/12.0.1/dbhome_1/network/admin/listener.ora

How do I get it to work so that the listener.ora is the file that is used instead?


texts:
Consider the following table,

CREATE TABLE temp (
    [TabName] VARCHAR(255),
    [ID] VARCHAR(255),
    [AsOfDate] DATE)

  INSERT INTO temp VALUES
    ('TAB1', 'C103', '2019-05-01'),
    ('TAB1', 'C103', '2019-06-01'),
    ('TAB2', 'C103', '2019-06-01'),
    ('TAB2', 'C103', '2019-07-01'),
    ('TAB1', 'C103', '2019-09-01'),
    ('TAB1', 'C104', '2019-06-01'),
    ('TAB1', 'C104', '2019-08-01')

TabName     ID      AsOfDate
TAB1        C103     '2019-05-01'
TAB1        C103     '2019-06-01'
TAB2        C103     '2019-06-01'
TAB2        C103     '2019-07-01'
TAB1        C103     '2019-09-01'
TAB1        C104     '2019-06-01'
TAB1        C104     '2019-08-01'
TAB1        C105     '2019-04-01'
TAB1        C105     '2019-05-01'

I am wanting to find the IDs from the table that have skipped dates. In this table, I would want to identify that ID C103 and C104 have skipped dates as they have jumped from '2019-07-01' to '2019-09-01' and '2019-06-01' to '2019-8-01' respectively.

I have found the following question asked previously Find Missing Dates in Data which I believe gives some potential clues on the approach, i.e. using CTE, however, I am unsure how to apply them to this question where the dates are not consecutive across the whole table.

Should we be looking at partitioning here?


texts: Is Audit plugin available for Community edition?

texts:
MySQL version 5.7.26 MySQL Community Server (GPL)

OS : CentOS 7.6

I was trying to install audit plugin for MySQL database. But, I encountered the following error

mysql> INSTALL PLUGIN audit_log SONAME 'audit_log.so';
ERROR 1142 (42000): INSERT command denied to user 'root2'@'10.80.16.236' for table 'plugin'
mysql>

I would like to know if there is an Audit plugin available for Community edition.
If so, how can I install it?

I don't see the audit plugin in the plugin directory

# pwd
/usr/lib64/mysql/plugin
# ls -l
total 59556
-rwxr-xr-x. 1 root root   103728 Apr 13 14:36 adt_null.so
-rwxr-xr-x. 1 root root   356976 Apr 13 14:36 authentication_ldap_sasl_client.so
-rwxr-xr-x. 1 root root    43552 Apr 13 14:36 auth_socket.so
-rwxr-xr-x. 1 root root   940312 Apr 13 14:36 connection_control.so
drwxr-xr-x. 2 root root     4096 May  3 04:03 debug
-rwxr-xr-x. 1 root root 21640312 Apr 13 14:36 group_replication.so
-rwxr-xr-x. 1 root root   483520 Apr 13 14:36 ha_example.so
-rwxr-xr-x. 1 root root   968432 Apr 13 14:36 innodb_engine.so
-rwxr-xr-x. 1 root root   957088 Apr 13 14:36 keyring_file.so
-rwxr-xr-x. 1 root root   460064 Apr 13 14:36 keyring_udf.so
-rwxr-xr-x. 1 root root  1184680 Apr 13 14:36 libmemcached.so
-rwxr-xr-x. 1 root root  8973984 Apr 13 14:36 libpluginmecab.so
-rwxr-xr-x. 1 root root    21424 Apr 13 14:36 locking_service.so
-rwxr-xr-x. 1 root root    53928 Apr 13 14:36 mypluglib.so
-rwxr-xr-x. 1 root root    41088 Apr 13 14:36 mysql_no_login.so
-rwxr-xr-x. 1 root root 22243648 Apr 13 14:37 mysqlx.so
-rwxr-xr-x. 1 root root    49504 Apr 13 14:36 rewrite_example.so
-rwxr-xr-x. 1 root root   590936 Apr 13 14:36 rewriter.so
-rwxr-xr-x. 1 root root   933904 Apr 13 14:36 semisync_master.so
-rwxr-xr-x. 1 root root   159928 Apr 13 14:36 semisync_slave.so
-rwxr-xr-x. 1 root root   209520 Apr 13 14:36 validate_password.so
-rwxr-xr-x. 1 root root   506320 Apr 13 14:36 version_token.so
#


texts:
I have a roads table. The road lines are stored in a user-defined type column called SHAPE.

Normally, spatial entities just have x and y dimensions. In my case, my roads have a measure(m) dimension; which means that I have x, y, and m dimensions. (M values are used for linear referencing purposes.)

I have a problem where my GIS software does not automatically reset the m values of the lines when the x and y values are edited. This means that after lines are edited, the m values become out-of-date/incorrect.

I've successfully figured out how to generate new m values. Admittedly, the query is long and convoluted, but that's not the problem at hand. I'm working on simplifying it here.

select SDE.ST_GEOMFROMTEXT('LINESTRING M ( ' || listagg(X||' '||Y||' '||m,',') within group (order by VERTEX_INDEX) || ')', 300049)  as WKT
 from (select ROAD_ID, VERTEX_INDEX, X, Y, ROUND(sum(m) over (partition by ROAD_ID order by VERTEX_INDEX),3) as m
         from (select ROAD_ID, VERTEX_INDEX, X, Y, case when VERTEX_INDEX = 1 then 0 else SQRT(power(abs((lag(X,1) over (partition by ROAD_ID order by VERTEX_INDEX))-X),2)+power(abs((lag(Y,1) over (partition by ROAD_ID order by VERTEX_INDEX))-Y),2)) end as m
            from (
                select
                    ROAD_ID,
                    t.VERTEX_INDEX,
                    SDE.ST_X(SDE.ST_POINTN(SHAPE, t.VERTEX_INDEX)) as X,
                    SDE.ST_Y(SDE.ST_POINTN(SHAPE, t.VERTEX_INDEX)) as Y
                from
                    USER1.ROADS
                cross join
                    (
                    select level as VERTEX_INDEX
                    from DUAL connect by level <=
                        (
                        select max(SDE.ST_NUMPOINTS(SHAPE))
                        from USER1.ROADS
                        )
                    ) t
                where
                    t.VERTEX_INDEX <= SDE.ST_NUMPOINTS(SHAPE)
                    and SDE.ST_GEOMETRYTYPE(SHAPE) <> 'ST_MULTILINESTRING'
                )
            )
      )
group by ROAD_ID

Result:
LINESTRING M ( 670495.21962780 4861662.29203628 0.00000000, 670644.70911652 4862034.07146977 400.70800781, 670579.99977367 4862214.15467259 592.06494141)

Question:

Once the SHAPE column is edited (user updates the x and y), I want to take that new shape, reset the m values with my query, then update the SHAPE column with the reset m values.

I think the solution would be a fairly simple trigger. Unfortunately, I'm quite new at triggers, and am failing dramatically at making this one. How can this be done with a trigger?

Update:

I've drummed up a basic trigger to start from:

create or replace trigger "RESET_M"
before update of SHAPE
on USER1.ROADS

for each row
declare
begin
    :new.SHAPE := :new.SHAPE;
end;

Of course, this isn't helpful. It just sets the new SHAPE to the same new SHAPE, which is useless.

I don't know how I can do what I want by using my query. I want to get the edited/updated x and y, calculate new m values, and of course, commit it to the SHAPE column. I imagine this would be quite easy if I were using functions. But the underlying reason why my giant query exists is that there isn't a function to do what I need to do.

And yes, Oracle Spatial does have a function called SDO_LRS.REDEFINE_GEOM_SEGMENT that does what I want. But I unfortunately, I don't have Oracle Spatial.


texts: How to improve restoring the PostgreSQL database?

texts:
I'd like to restore the PostgreSQL database from a dump. The below command works fine for me:

CALL "%PGBIN%\pg_restore" -c -d NetCatalog %DUMPFILE%

How can I add a few lines to my .bat file to rename the existing NetCatalog to NetCatalog_Old and create a new database NetCatalog before calling the above line?

PS. Is there an argument that allows me to log the restoring process?

Thanks!


texts: Advantage of querying data outside a filtered index

texts:
Is there any way to take an advantage of having a filtered index to speed DML queries targeting data sets outside that index?

My playground:

Table with billion rows and 100 columns
Unique clustered index on column C1
Filtered index on column C2 covering 90% of rows defined as:

CREATE NONCLUSTERED INDEX IX_NC_F_Tab_C2 ON TAB(C2) WHERE C2 >= 0;

What I would like to achieve is the most optimal way to delete everything from that table which is not covered by the filtered index, like:

DELETE FROM Tab WHERE NOT C2 >= 0;

In this scenario my IX_NC_F_Tab_C2 index won't bring any improvement to the execution plan, therefore is there any way to force the estimator to somehow use it?


texts: Performance implications of the NEWSEQUENTIALID() re-sequence on restart

texts:
I see a very good case why I should use NEWSEQUENTIALID() as PK. It would be internal to my system and has good performance similar to IDENTITY() but better than NEWID().

However, NEWSEQUENTIALID() is not sequential after SQL Server restart. After restarting Windows, the GUID can start again from a lower range, but is still globally unique. So, would It be a bad choice for a primary key - as bad as using normal guids in terms of performance - because of Fragmentation etc?

There is already a question on this - but can somebody explain this more please.


texts: How to preserve the number of errors at the level of the package in SQL or .txt file?

texts:
This is my package.

Does anyone know how to preserve the number of errors at the level of the package in SQL or .txt file? I'm new in SSIS.
Thank you.


texts: How to get TABLE like dynamic structure output/returned from PostgreSQL function?

texts:
I am not very experienced with PLPGSSQL hence seeking some guidance to achieve following...
I have an Oracle Procedure which i have to convert to PostgreSQL. This oracle function has declared a TABLE TYPE with few columns and based on certain logic it returns the refcursor.
The refcursor is giving the output after executing dynamic query in which the actual table name and the column names can differ in each call of the function. Basically the data is metadata about tables and based on the selections at application side the data is selected from dynamic queries and returned. So for example, some time the same function may return 3 columns from TABLE A and some time it may give 8 columns from Table A & B joined together.
There are lot of functions that has such structure (Table type and refcursor as output) in Oracle which i would need to convert at later stage.
Now when i am trying to convert it to PLPGSQL for PostgreSQL 11, i am facing following challenges

The TABLE TYPE would be different in each function so I am avoiding to define the custom TYPE to be used in each function (That would be too much of object creation). I am rather trying to have it defined in individual function as ROWTYPE but doing so would require me to loop through the code to get multiple records
How do i convert the output refcursor to a TABLE and give it back to calling function as a ResultSET (multiple rows and columns)?
I am avoiding to have a need for any kind of loop in my logic.

Thank you in advance.


texts: How to delete data from both tables or least one of them, when using "delete from ... using ... where" in Postgresql?

texts:
I have this sql script for my Postgresql db:

  DELETE FROM table1
  USING table2
  WHERE table2.frg_table1_id = table1.id
  AND table1.id = $1

I want to it to always delete data from table1 if the condition table1.id = $1 is met. And, optionally, delete corresponding data from table2 too. Even when  corresponding data in table2 doesn't exist, it still must delete from table1

An issue now is that, it'll delete data either from both tables or from none.

How should I twist my script?


texts:
How would I go about grouping data according to values given in a separate file/table?

For example. I have one column in the table I want to be grouped, called ID, and a separate CSV file with IDs to group the data by. I have not created the CSV file yet, so if You think there is a better way to store these values, feel free to tell. The CSV file could for example look like:

ID1,ID2,ID3;ID4,ID5;ID6,ID7,ID9; ... ;

In this case I would want to group the data by ID1 + ID2 + ID3 and ID4 + ID5 and ID6+ID7+ID9 and so on. The point is, that the groups are consistent, but the data is frequently changed and the tables updated. We are talking of hundreds of groups, so it would be neat to organize the groups into a separate file.


texts: Unable to create an user using mysql_embedded

texts:
I am creating a docker mariadb image and wanted to create a database and user name before bootstrapping mysqld daemon. So as per the documentation I tried below queries.

mysql_embedded -e "CREATE DATABASE IF NOT EXISTS test ;"

mysql_embedded -e "CREATE USER admin@localhost IDENTIFIED BY 'password';"

mysql_embedded -e "select user, host, password, plugin from mysql.user;"

The above query successfully create a database and display user information. But it didn't create the user i.e the middle query didn't get executed. Even it didn't show any error. Can't I create an user using mysql_embedded ?

What is the alternative to mysql_embedded. Is it mysqld --bootstrap ?

Here is a blogpost on mysql_embedded https://mariadb.com/resources/blog/using-mysql_embedded-and-mysqld-bootstrap-to-tinker-with-privilege-tables/


texts: Stored procedures vs inline SQL in a Job step

texts:
I have created a job with two steps:

Delete 50000 rows from an error table older than 90 days.
Create and email a quick report on the number of new records created in the error table that day, literally a:

SELECT count_big(*)
FROM table
WHERE datevalue < GETUTCDATE()
AND datevalue > DATEADD(DAY,-1,GETUTCDATE())

...but with some happy formatting to make an email table.

Currently I have step 2 running SQL in the job step rather than calling a stored procedure.

My question is, does it make a difference (performance or otherwise) in SQL Server 2014, to run a job step that calls a stored procedure or one that executes inline SQL?


texts:
I have this trigger in DB2 that I need to convert to ORACLE, but I don't know how it works in  DB2 so i can translate it to a ORACLE one, I think it doesn't do anything.

CREATE TRIGGER ERRLOG_TRIGGER
  NO CASCADE BEFORE INSERT
  ON ERROR_LOG
  REFERENCING
    NEW AS NEWROW
  FOR EACH ROW
BEGIN ATOMIC
     SET NEWROW."ERRLOG_CREATEDON" = CURRENT TIMESTAMP;
END;

I see that date update, but what is it for?


texts: Normalized Data Store - Confused with prefixes to use

texts:
I'm designing a Staging+NDS+DDS Data Warehouse system, where an ETL is going to normalize data from [Staging] and load it into [NDS], which will hold all history.

I've pretty much finished the T-SQL script that will create the tables and constraints in the [NDS] database, which contains Master and Transactional tables, that will respectively feed [DDS] Dimension and Fact tables in what I'm intending to be a star schema.

I'm given myself the following rules to follow:

Tables sourcing [DDS] dimensions are prefixed with DWD_
Tables sourcing [DDS] facts are prefixed with DWF_
Foreign key columns are prefixed with DWK_
Surrogate key column is prefixed with the same prefix as the table. Which means the surrogate key is always either:

DWD_Key for a DWD_ table, or
DWF_Key for a DWF_ table.

Control columns are prefixed with the same prefix as the table. For example...

The DWD_Customers table has control columns:

DWD_IsLastImage
DWD_EffectiveFrom
DWD_EffectiveTo
DWD_DateInserted
DWD_DateUpdated
DWD_DateDeleted

The DWF_InvoiceHeaders table has control columns:

DWF_DateInserted
DWF_DateUpdated
DWF_DateDeleted

Primary keys (/surrogate keys) are always prefixed with PK_ followed by the table name (including the table prefix) - e.g. PK_DWD_Customers and PK_DWF_InvoiceHeaders.
I also added a unique constraint on natural keys, and those are always prefixed with NK_ followed by the table name (including the table prefix) - e.g. NK_DWD_Customers and NK_DWF_InvoiceHeaders.
Foreign key columns are always prefixed with DWK_ followed by the name of the referenced table (without its prefix) and the word "Key" - e.g. DWK_CustomerKey.
Foreign key constraints are always named FK_[ParentTableNameWithPrefix]_[ChildTableNameWithPrefix].
When a table has multiple FK's to the same table, the name of the FK column is appended to the constraint's name, e.g. FK_DWD_FiscalCalendar_DWF_OrderDetails_DeliveryDate.

All prefixed columns have no business meaning and should never appear in views; this leaves me with, I find, a pretty clean and consistent design, and create table scripts looking like this:

create table DWD_SubCategories (
     DWD_Key int not null identity(1,1)
    ,DWD_DateInserted datetime not null
    ,DWD_DateUpdated datetime null
    ,DWK_CategoryKey int not null
    ,Code nvarchar(5) not null
    ,Name nvarchar(50) not null
    ,constraint PK_DWD_SubCategories primary key clustered (DWD_Key asc)
    ,constraint NK_DWD_SubCategories unique (Code)
);

So, my question is, is there anything I should know (or unlearn) before I continue and implement the ETL to load data into this database? Would anyone inheriting this database want to chase me down and rip my head off in the future? What should I change to avoid this? The reason I'm asking about prefixes, is because I'm using DWD and DWF, but the tables are technically not "dimension" and "fact" tables. Is that confusing?

Also, I'm unsure about the concept of natural key - am I correct to presume it should be a unique combination of columns that the source system might consider its "key" columns, that I can use in the ETL process to locate, say, a specific record to update?


texts:
i have cloud vps server, cpu 512 mb.

i have website. if i hold f5 in browser after 10-20 sec mysql server no responding and it gives me error

SQLSTATE[HY000] [2002] Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)

i also installed drupal, open home page and hold f5, it also crashes mysql.

after that mysql server need to restart service mysqld restart

i think problem is in mysql server configuration.

any advice?

server centos 6

log file after f5 hold

    160502 10:42:45 mysqld_safe Number of processes running now: 0
160502 10:42:45 mysqld_safe mysqld restarted
160502 10:42:45 [Note] /usr/libexec/mysqld (mysqld 5.5.48) starting as process 12843 ...
160502 10:42:45 [Note] Plugin 'FEDERATED' is disabled.
160502 10:42:45 InnoDB: The InnoDB memory heap is disabled
160502 10:42:45 InnoDB: Mutexes and rw_locks use GCC atomic builtins
160502 10:42:45 InnoDB: Compressed tables use zlib 1.2.3
160502 10:42:45 InnoDB: Using Linux native AIO
160502 10:42:45 InnoDB: Initializing buffer pool, size = 128.0M
InnoDB: mmap(137363456 bytes) failed; errno 12
160502 10:42:45 InnoDB: Completed initialization of buffer pool
160502 10:42:45 InnoDB: Fatal error: cannot allocate memory for the buffer pool
160502 10:42:45 [ERROR] Plugin 'InnoDB' init function returned error.
160502 10:42:45 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
160502 10:42:45 [ERROR] Unknown/unsupported storage engine: InnoDB
160502 10:42:45 [ERROR] Aborting

160502 10:42:45 [Note] /usr/libexec/mysqld: Shutdown complete

160502 10:42:45 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended


texts: Is it possible to use Kibi with Elasticsearch 5.2?

texts:
We are in the process of migrating our MSSQL database to an elasticsearch platform, and have run into the (apparently common) problem of many parents to one child relationship, when trying to access information from multiple tables.
It seems Kibi is capable of allowing these type of relationships to exist, however has only been optimized for ES 4.2. Has anyone found a way to use Kibi , as opposed to Kibana on the newest ES?

PS. changing the DB is not an option, as this is a proof of concept only.


texts: delete hung soon after vacuum verbose analyse

texts:
I have a fairly large java webapp system that connects to a postgres8.4 db on rhel.
This app only does inserts and reads.

This has been running without issue for 2.5 years and for the last year or so I implemented a data deletion script which deletes up to 5000 parent records and the corresponding child data in 3 tables.

This deletion script is called from a cron job every 5 minutes.  It has been working flawlessly for over a year.  This script always takes about 1-3 seconds to complete.

Also called from cron is vacuum verbose analyse script which is just called once a day (a few minutes before a deletion).  This also has been working flawlessly for over a year.  This takes about 15 minutes to complete.

Now last weekend (Saturday), the vacuum kicked off at 14:03, the deletion kicked off at 14:07 and did not complete.  The next deletion kicked off at 14:12 and encountered a deadlock.  The webapp at 14:14 hung.  At 14:16 everything resumed as per normal and has been running fine since.

Now

To add more confusion, this server has an almost identical setup (standby sever) however the vacumm cron is due to run at 02:03 in the morning.  When the vacuum kicked off on Sunday at 02:03, the same situation as above was encountered and at 02:14 the java app hung resuming at 02:15.

More

To further confuse me:
every time I go to the site I takes reading of df - this is always around 40% but after this happened, the df now reports 5% less

Any ideas?  Please let me know if I have left out any relevant information.

edit

This is the Postgresql-Sat.log

WARNING:  skipping "pg_authid" --- only superuser can vacuum it
WARNING:  skipping "pg_database" --- only superuser can vacuum it
WARNING:  skipping "pg_tablespace" --- only superuser can vacuum it
WARNING:  skipping "pg_pltemplate" --- only superuser can vacuum it
WARNING:  skipping "pg_shdepend" --- only superuser can vacuum it
WARNING:  skipping "pg_shdescription" --- only superuser can vacuum it
WARNING:  skipping "pg_auth_members" --- only superuser can vacuum it
ERROR:  deadlock detected
DETAIL:  Process 12729 waits for ShareLock on transaction 91311423; blocked by process 12800.
    Process 12800 waits for ShareLock on transaction 91311422; blocked by process 12729.
    Process 12729: delete from child1 where id in
    (
    select id from parent where date_collected < now() - interval '13 months' order by id limit 5000
    );
    Process 12800: delete from child1 where id in
    (
    select id from parent where date_collected < now() - interval '13 months' order by id limit 5000
    );


texts: Deleting millions of records on SQL Server 14.0

texts:
I have two tables containing 200 Millions of records each.
I have to delete from them, approximately 70 Millions of records, based on an integer value in a column.

I'm deleting them in chunks of 4000, using the following script:

DECLARE @BATCHSIZE INT, @ITERATION INT, @TOTALROWS INT, @MSG VARCHAR(500)
DECLARE @STARTTIME DATETIME, @ENDTIME DATETIME

SET NOCOUNT ON;
SET DEADLOCK_PRIORITY LOW;
SET @BATCHSIZE = 4000
SET @ITERATION = 0
SET @TOTALROWS = 0

WHILE @BATCHSIZE>0
BEGIN
    SET @STARTTIME = GETDATE();

    BEGIN TRANSACTION
    DELETE TOP(@BATCHSIZE)
    FROM [mydb].[dbo].tableA
    WHERE [mydb].[dbo].tableA.Code not IN (
            SELECT Code
            FROM [mydb].[dbo].TableB)

    SET @BATCHSIZE=@@ROWCOUNT
    SET @ITERATION=@ITERATION+1
    SET @TOTALROWS=@TOTALROWS+@BATCHSIZE
    COMMIT TRANSACTION;

    SET @ENDTIME = GETDATE();

    SET @MSG = 'Iteration: ' + CAST(@ITERATION AS VARCHAR) + ' Total deletes:' + CAST(@TOTALROWS AS VARCHAR) + '  >> ' + CAST(DATEDIFF(millisecond, @STARTTIME,@ENDTIME) AS VARCHAR)
    RAISERROR (@MSG, 0, 1) WITH NOWAIT
END

TableA contains 6 columns, 5 of integers and one NVARCHAR(64).
There is an index on the column Code, and clusterIndex on the PK.
TableB contains only one column, Code,it's a PK.

After running the script for a couple of hrs, it becames very very slow.

At the beginning each iteration was executed in 250ms, then it increase to 2 minutes after running for some hours.

The database is in simple recovery mode. It's not used by anyone, and it's running on a dedicated machine with 256GB of RAM.

I've tried to rebuild the indexes every hour, Shrink the database (not the file because my user can't) but it's always slow.

If I start to delete the records on another table it has the exact same behaviour, starting very very fast and then increase to slow down after every iteration.

How can I restore the initial conditions? What can I do to improve the delete?
I've tried to


texts: NEWB Getting to KNow New Database and Understand Where Relationships Are

texts:
I'm teaching myself SQL using MYSQL and a textbook.

While working through the textbook I'm using a database I'm kinda familiar with - an exported copy of my CMS driven websites database.

My website collects and sends emails to people who subscribe each time a new article is added. When I log in to the back end of my CMS website I can see the number of emails sent to each email address. People sign up via the "get alerts" page.

While reading through my text book I am currently covering functions in SQL. Since I am already familiar with my data I thought that it would be a good opportunity to try to recreate the data described above - the count of emails sent to each recipient.

There are many tables in my database but I'm pretty sure that the ones of relevance are called "alert" and "alert_sent".

I have used SELECT to view the top 5 records and try to determine what a common field between the two would be but cannot see any. I then wondered what the standard procedure here was?

When one is given a new database, is there a rule of thumb in figuring out what fields are what? Is there typically a "key" of some sort that defines what tables/fields are showing? Or do DBAs typically have to just browse the data and figure it out?

Apologies if this question is vague. I'm trying to teach myself by example in using a database I'm moderatley familiar with.

If it's only help, here is the results of DESCRIBE on the two mentioned tables:

Table "alert"

Field              | Type                | Null | Key | Default | Extra          |
+--------------------+---------------------+------+-----+---------+----------------+
| id                 | bigint(20) unsigned | NO   | PRI | NULL    | auto_increment |
| user_id            | int(11) unsigned    | YES  | MUL | 0       |                |
| alert_type         | tinyint(4)          | NO   |     | NULL    |                |
| alert_recipient    | varchar(200)        | YES  | MUL | NULL    |                |
| alert_code         | varchar(30)         | YES  | UNI | NULL    |                |
| alert_confirmed    | tinyint(4)          | NO   |     | 0       |                |
| alert_lat          | varchar(150)        | YES  |     | NULL    |                |
| alert_lon          | varchar(150)        | YES  |     | NULL    |                |
| alert_radius       | tinyint(4)          | NO   |     | 20      |                |
| alert_ip           | varchar(100)        | YES  |     | NULL    |                |
| previous_noconfirm | tinyint(4)          | YES  |     | NULL    |

and table "alert_sent"

Field       | Type                | Null | Key | Default | Extra          |
+-------------+---------------------+------+-----+---------+----------------+
| id          | bigint(20) unsigned | NO   | PRI | NULL    | auto_increment |
| incident_id | bigint(20) unsigned | NO   | MUL | NULL    |                |
| alert_id    | bigint(20) unsigned | NO   | MUL | NULL    |                |
| alert_date  | datetime            | YES  |     | NULL    |


texts: How to create a new instance of PostgresSQL on OS-X 10.8 Server

texts:
After upgrading my MacMini home server from OS-X 10.7 (Lion) Server to OS-X 10.8 (ML) + Server.app, I saw PostgreSQL 9.1.4 was released.

Unfortunately, the setup process for PostgreSQL 9.1.4 has created these brand new databases: collab, webauth, postgres, tempalte0, template1; all the other databases and roles created by me seem to have been deleted!

To better understand what has happened:

> Which postgres

OSX 10.7.4: /usr/bin/postgres
OSX 10.8: /Applications/Server.app/Contents/ServerRoot/usr/bin/postgre

$PGDATA is now on:

OSX 10.7.4: "/var/pgsql"
OSX 10.8 "/Library/Server/PostgreSQL/Data"

After the update the /var/pgsql folder is gone.

Since I do not want to repeat this experience in future updates, I think it is a good idea to create my database on a separate cluster/instance.

How would advise me from here?

Is there any other way to avoid losing my databases?
Where and how should I create my own databases without interfering with Apple's databases?
How can I configure and start a second instance of PostgreSQL on the same OS-X server?


texts: Why the instance name of SQL Server Express is changing?

texts:
I'm workig on a web app which is using

  SQL Server Express (Microsoft SQL Server 2016 (SP1) (KB3182545) -
  13.0.4001.0 (X64)   Oct 28 2016 18:17:30   Copyright (c) Microsoft Corporation  Express Edition (64-bit) on Windows 10 Pro 6.3
  (Build 18362: )).

The web app is using connection string to connect to DB. After searching for a few days for a proper connection string format I managed to find the format, using method described by Nik in this blog post and it is:

Data Source = np:\\.\pipe\LOCALDB#A9DE3E14\tsql\query; Initial Catalog = MyDataBaseCatalog; User ID = myuser; Password=mypassword;

Everything was working fine yesterday, but today it stopped. I discovered that the service name has changed to:

LOCALDB#B87CB983

After replacing the instance name with the new service name in the connection string the web app works well, but it's not an acceptable solution.

Can you tell me why the instance name is changing, is it any Express version limitation? How can configure the name to be permanent?


texts: How to count matching values and print 0 for non-matching value in PostgreSQL?

texts:
I have two tables as follows:

create table first (f_id int,rank varchar(5), primary key (f_id));
create table second(s_id int,assigned int,f_id int,primary key(s_id),foreign key(f_id) references first(f_id));

Then I have inserted some records in these tables:

insert into first values (1,'A'),(2,'B'),(3,'C');
insert into second values(1,3,1),(2,5,2),(3,7,2),(4,1,2);

Now I want to select all records from these two tables where if first.f_id=second.f_id then it should count the assigned column's value, but if it does not match then it should return print 0.
My desired output would be:

rank         assigned
 A               1
 B               3
 C               0

How can I achieve this?


texts: My Fact table has data weekly but it is not visible in SSAS cube

texts:
I have my fact table that has data in weekly basis, so I am mapping with Week dimension to pull records in Piviot table.  My ssas cube doesnt show all data that is in fact. Should I add any specific scope to show in cube.
for Example, My fact has data for week 201839 as well as 201840 (Sep month weeks)
but in cube I can see only for one week  201839 but not for 201840


texts: Rename all databases when doing a mongorestore?

texts:
I have a datadump but I need to change the name of every database while restoring them,
if a database is named "customers" it needs to be "customer2", "products" to "products2"
I can rename an individual database but that will take a lot of time to rename each database separately
mongorestore --nsFrom='PetHotel.pets' --nsTo='PetHouse.pets' --nsInclude="PetHouse.*" dump/
how to I rename all database in a dump?


texts: Select records between different work shifts depending on datetime

texts:
I have a data table called "Requests" that records the requests made by employees in a company, I used to use a Stored Procedure to get all the request from a single workday (starting at 6am and ending at 6am of the next day)

USE [DataBase]
GO
/****** Object:  StoredProcedure [dbo].[GetRequests]  ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[GetRequests]
as begin
DECLARE @from datetime
set @from = getdate();
if (DATEPART(HOUR, @from) < 6) set @from = dateadd(day, -1, @from);

DECLARE @start_date datetime = CONVERT(datetime, CONVERT(char(9), @from, 112) + '06:00');
DECLARE @end_date datetime = DATEADD(HOUR, 24, @start_date)

select * FROM Requests
where RequestDate between  @start_date and @end_date
end

Now I need another stored procedure to get all the request from a specific work shift.

The company has 3 work shifts (1st: 6am to 2pm / 2nd: 2pm to 10pm / 3rd: 10pm to 6am).

Is there a way to get all the requests from a single shift?

For example: if the current time is 5am, to only show the requests from the 3rd shift (10pm to 6am) and if it is 3pm to only show the requests from the 2nd shift (2pm to 10pm)


texts: MySQL ranking one user based on cell value

texts:
I got to figure out how to optimally check for a users rank in all the stages he has passed in a game. The rank is based on the amount of time (float) it took for the player to pass the stage.

What I've got is this schema: http://www.sqlfiddle.com/#!9/3764e/1

id| fTime | stage |
-------------------
1 | 11.123| first |
1 | 12.561| second|
1 | 13.333| third |
2 | 11.642| first |
2 | 12.231| second|
2 | 13.999| third |
3 | 11.000| first |
3 | 12.842| second|
3 | 13.849| third |
4 | 11.535| first |
4 | 12.653| second|
4 | 13.145| third |
5 | 11.653| first |
5 | 12.134| second|
5 | 13.678| third |
6 | 11.546| first |
6 | 12.325| second|
6 | 13.532| third |

The result that I need is the players rank in each stage he has passed, based on his time in it (fTime).

|     id|     stage| rank|
|      1|   'first'|    1|
|      1|  'second'|    2|
|      1|   'third'|    1|

The way I've been doing this thus far, is by querying the stages the player has finished, then the time he has achieved in each stage, and then getting his rank on that stage. This is obviously not optimal, as I got to do a lot of trips to the mysql server and back, when it could be done in way less queries.

I've been looking around the net for issues like this, but haven't found anything that quite matches, and this goes beyond my SQL skills..


texts: TSQL SELECT statement to list a column calculated based on the value of a field

texts:
I have a table named Table1, with three fields Project ID, EmployeeID, Starting Date.
Need a select statement to list all columns of the table, and an additional column to show the number of project by employee since a year. See below figure.

It should be enough to get the count(distinct EmployeeID) where StartingDate > 1/1/2016. But, I can't figure out the query. Could anyone please help?


texts: Can a High number of Worktables/sec and/or workfiles/sec lead to tempdb Contention?

texts:
We have a large number of very heavily used OLTP systems. One of our biggest issues right now is tempdb contention. I understand how creating many temp tables and table variables (which our developers love to do) will cause contention on GAM/SGAM/PFS pages, I am curious whether workfiles/worktables will also cause contention on those pages? My brain says yes, but I can't find anything to corroborate that.

The reason I'm concerned is that I don't want to be only tackling the temp table creation issue, but if we also need to get worktables and workfiles under control too.

For example one of our DB's currently has these numbers (slightly off peak):

Temp table Creation Rate: 30/sec

Workfiles / sec: 20000

Worktables / sec: 800


texts: How to access to DB Instance from another DB Instance with Windows Authentication

texts:
I have two database servers

DB_INSTANCE1
DB_INSTANCE2

Is it possible to access from DB_INSTANCE1\DB1 SQL script to DB_INSTANCE2\DB2 tables without using SQL Logins? (not every database servers are in MIXED mode)

Can I somehow set this in terms to use only AD security groups or DB roles?
What is the recommendation practice?